Oracle บทช่วยสอนทริกเกอร์ PL/SQL: แทนที่จะใช้แบบผสม [ตัวอย่าง]

ทริกเกอร์ใน PL/SQL คืออะไร

ทริกเกอร์ เป็นโปรแกรมที่เก็บไว้ซึ่งถูกสั่งงานด้วย Oracle เอ็นจิ้นโดยอัตโนมัติเมื่อมีการดำเนินการคำสั่ง DML เช่นการแทรก, อัปเดต, ลบบนตารางหรือเหตุการณ์บางอย่างเกิดขึ้น รหัสที่จะดำเนินการในกรณีของทริกเกอร์สามารถกำหนดได้ตามความต้องการ คุณสามารถเลือกเหตุการณ์ที่ต้องทริกเกอร์และกำหนดเวลาในการดำเนินการได้ วัตถุประสงค์ของทริกเกอร์คือเพื่อรักษาความสมบูรณ์ของข้อมูลในฐานข้อมูล

ประโยชน์ของทริกเกอร์

ต่อไปนี้คือประโยชน์ของทริกเกอร์

• การสร้างค่าคอลัมน์ที่ได้รับบางส่วนโดยอัตโนมัติ
• การบังคับใช้ความสมบูรณ์ของการอ้างอิง
• การบันทึกเหตุการณ์และการจัดเก็บข้อมูลในการเข้าถึงตาราง
• การตรวจสอบบัญชี
• Syncการจำลองแบบตารางอย่างแม่นยำ
• การกำหนดสิทธิการรักษาความปลอดภัย
• ป้องกันการทำธุรกรรมที่ไม่ถูกต้อง

ประเภทของทริกเกอร์ใน Oracle

สามารถจำแนกทริกเกอร์ได้ตามพารามิเตอร์ต่อไปนี้

• จำแนกตาม ระยะเวลา
• BEFORE Trigger: มันจะเริ่มทำงานก่อนที่เหตุการณ์ที่ระบุจะเกิดขึ้น
• AFTER Trigger: มันจะเริ่มทำงานหลังจากเหตุการณ์ที่ระบุเกิดขึ้น
• แทนทริกเกอร์: ประเภทพิเศษ คุณจะได้เรียนรู้เพิ่มเติมเกี่ยวกับหัวข้อต่อไป (สำหรับ DML เท่านั้น)
• จำแนกตาม ระดับ
• ทริกเกอร์ระดับคำสั่ง: มันจะยิงหนึ่งครั้งสำหรับคำสั่งเหตุการณ์ที่ระบุ
• ทริกเกอร์ระดับ ROW: จะเริ่มทำงานสำหรับแต่ละเรกคอร์ดที่ได้รับผลกระทบในเหตุการณ์ที่ระบุ (สำหรับ DML เท่านั้น)
• จำแนกตาม อีเว้นท์
• ทริกเกอร์ DML: มันจะเริ่มทำงานเมื่อมีการระบุเหตุการณ์ DML (INSERT/UPDATE/DELETE)
• ทริกเกอร์ DDL: เริ่มทำงานเมื่อมีการระบุเหตุการณ์ DDL (CREATE/ALTER)
• ทริกเกอร์ฐานข้อมูล: เริ่มทำงานเมื่อมีการระบุเหตุการณ์ฐานข้อมูล (LOGON/LOGOFF/STARTUP/SHUTDOWN)

ดังนั้นแต่ละทริกเกอร์จึงเป็นการรวมกันของพารามิเตอร์ข้างต้น

วิธีการสร้างทริกเกอร์

ด้านล่างนี้คือไวยากรณ์สำหรับการสร้างทริกเกอร์

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

คำอธิบายไวยากรณ์:

• ไวยากรณ์ข้างต้นแสดงคำสั่งทางเลือกต่างๆ ที่มีอยู่ในการสร้างทริกเกอร์
• BEFORE/ AFTER จะระบุช่วงเวลาของกิจกรรม
• แทรก/อัปเดต/เข้าสู่ระบบ/สร้าง/ฯลฯ จะระบุเหตุการณ์ที่ต้องเรียกใช้ทริกเกอร์
• ส่วนคำสั่ง ON จะระบุว่าเหตุการณ์ที่กล่าวถึงข้างต้นนั้นถูกต้องบนวัตถุใด ตัวอย่างเช่น นี่จะเป็นชื่อตารางที่อาจเกิดเหตุการณ์ DML ในกรณีของทริกเกอร์ DML
• คำสั่ง “FOR EACH ROW” จะระบุทริกเกอร์ระดับ ROW
• เมื่อส่วนคำสั่งจะระบุเงื่อนไขเพิ่มเติมที่ทริกเกอร์จำเป็นต้องเริ่มทำงาน
• ส่วนการประกาศ ส่วนการดำเนินการ ส่วนการจัดการข้อยกเว้นจะเหมือนกับส่วนอื่น บล็อก PL/SQL- ส่วนการประกาศและส่วนการจัดการข้อยกเว้นเป็นทางเลือก

:ใหม่และ:ข้อเก่า

ในทริกเกอร์ระดับแถว ทริกเกอร์จะเริ่มทำงานสำหรับแต่ละแถวที่เกี่ยวข้อง และบางครั้งจำเป็นต้องทราบค่าก่อนและหลังคำสั่ง DML

Oracle ได้จัดเตรียมสองส่วนคำสั่งไว้ในทริกเกอร์ระดับ RECORD เพื่อเก็บค่าเหล่านี้ เราสามารถใช้อนุประโยคเหล่านี้เพื่ออ้างถึงค่าเก่าและใหม่ภายในเนื้อหาของทริกเกอร์

• :ใหม่ - เก็บค่าใหม่สำหรับคอลัมน์ของตาราง/มุมมองฐานระหว่างการดำเนินการทริกเกอร์
• :OLD – เก็บค่าเก่าของคอลัมน์ของตาราง/มุมมองฐานระหว่างการดำเนินการทริกเกอร์

ข้อนี้ควรใช้ตามเหตุการณ์ DML ตารางด้านล่างจะระบุว่าส่วนคำสั่งใดถูกต้องสำหรับคำสั่ง DML ใด (INSERT/UPDATE/DELETE)

	INSERT	อัพเดท	ลบ
:ใหม่	ถูกต้อง	ถูกต้อง	ไม่ถูกต้อง. ไม่มีค่าใหม่ในกรณีการลบ
:เก่า	ไม่ถูกต้อง. ไม่มีค่าเก่าในกรณีแทรก	ถูกต้อง	ถูกต้อง

แทนทริกเกอร์

“INSTEAD OF trigger” เป็นทริกเกอร์ประเภทพิเศษ ใช้เฉพาะในทริกเกอร์ DML เท่านั้น ใช้เมื่อมีเหตุการณ์ DML เกิดขึ้นบนมุมมองที่ซับซ้อน

ลองพิจารณาตัวอย่างที่สร้างมุมมองจากตารางฐาน 3 ตาราง เมื่อมีการออกเหตุการณ์ DML ใดๆ บนมุมมองนี้ เหตุการณ์นั้นจะไม่ถูกต้องเนื่องจากข้อมูลที่นำมาจาก 3 ตารางที่แตกต่างกัน ดังนั้นในตัวทริกเกอร์นี้จึงใช้แทน ทริกเกอร์ INSTEAD OF ใช้เพื่อแก้ไขตารางฐานโดยตรง แทนที่จะแก้ไขมุมมองสำหรับเหตุการณ์ที่กำหนด

1 ตัวอย่าง:ในตัวอย่างนี้ เราจะสร้างมุมมองที่ซับซ้อนจากตารางฐานสองตาราง

• Table_1 คือตาราง emp และ

• Table_2 คือตารางแผนก

จากนั้นเราจะดูว่าทริกเกอร์ INSTEAD OF ถูกใช้เพื่อออกคำสั่ง UPDATE รายละเอียดของตำแหน่งในมุมมองที่ซับซ้อนนี้อย่างไร นอกจากนี้ เราจะดูด้วยว่า :NEW และ :OLD มีประโยชน์อย่างไรในทริกเกอร์

• ขั้นตอนที่ 1: การสร้างตาราง 'emp' และ 'dept' ด้วยคอลัมน์ที่เหมาะสม
• ขั้นตอนที่ 2: การเติมตารางด้วยค่าตัวอย่าง
• ขั้นตอนที่ 3: การสร้างมุมมองสำหรับตารางที่สร้างขึ้นข้างต้น
• ขั้นตอนที่ 4: อัปเดตมุมมองก่อนทริกเกอร์แทน
• ขั้นตอนที่ 5: การสร้างทริกเกอร์แทน
• ขั้นตอนที่ 6: อัปเดตมุมมองหลังจากทริกเกอร์แทน

ขั้นตอน 1) การสร้างตาราง 'emp' และ 'dept' ด้วยคอลัมน์ที่เหมาะสม

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept( 
Dept_no NUMBER, 
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

คำอธิบายรหัส

• รหัสบรรทัด 1-7: การสร้างตาราง 'emp'
• รหัสบรรทัด 8-12: การสร้างตาราง 'แผนก'

เอาท์พุต

สร้างตารางแล้ว

ขั้นตอน 2) ตอนนี้เมื่อเราสร้างตารางแล้ว เราจะเติมตารางนี้ด้วยค่าตัวอย่างและการสร้างมุมมองสำหรับตารางด้านบน

BEGIN
INSERT INTO DEPT VALUES(10,‘HR’,‘USA’);
INSERT INTO DEPT VALUES(20,'SALES','UK’);
INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); 
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30);
INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;
INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); 
COMMIT;
END;
/

คำอธิบายรหัส

• รหัสบรรทัด 13-19: การแทรกข้อมูลลงในตาราง 'แผนก'
• รหัสบรรทัด 20-26: การแทรกข้อมูลลงในตาราง 'emp'

เอาท์พุต

ขั้นตอน PL/SQL เสร็จ

ขั้นตอน 3) การสร้างมุมมองสำหรับตารางที่สร้างขึ้นข้างต้น

CREATE VIEW guru99_emp_view(
Employee_name:dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

คำอธิบายรหัส

• รหัสบรรทัด 27-32: การสร้างมุมมอง 'guru99_emp_view'

• รหัสบรรทัด 33: กำลังสอบถาม guru99_emp_view

เอาท์พุต

สร้างมุมมองแล้ว

ชื่อพนักงาน	DEPT_NAME	สถานที่ตั้ง
ZZZ	HR	สหรัฐอเมริกา
YYY	ขาย	UK
XXX	การเงิน	ประเทศญี่ปุ่น

ขั้นตอน 4) อัปเดตมุมมองก่อนทริกเกอร์แทน

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;
COMMIT;
END;
/

คำอธิบายรหัส

• รหัสบรรทัด 34-38: อัปเดตตำแหน่งของ “XXX” เป็น 'FRANCE' มันยกข้อยกเว้นเพราะ คำสั่ง DML ไม่ได้รับอนุญาตในมุมมองที่ซับซ้อน

เอาท์พุต

ORA-01779: ไม่สามารถแก้ไขคอลัมน์ที่แมปกับตารางที่ไม่ได้รักษาคีย์ไว้

ORA-06512: ที่บรรทัด 2

ขั้นตอน 5)เพื่อหลีกเลี่ยงข้อผิดพลาดระหว่างการอัปเดตมุมมองในขั้นตอนก่อนหน้า ในขั้นตอนนี้ เราจะใช้ "แทนทริกเกอร์"

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

คำอธิบายรหัส

• รหัสบรรทัด 39: การสร้างทริกเกอร์ INSTEAD OF สำหรับเหตุการณ์ 'UPDATE' ในมุมมอง 'guru99_emp_view' ที่ระดับ ROW ประกอบด้วยคำสั่ง update เพื่ออัพเดตตำแหน่งในตารางฐาน 'dept'
• รหัสบรรทัด 44: คำสั่งอัปเดตใช้ ': NEW' และ ': OLD' เพื่อค้นหาค่าของคอลัมน์ก่อนและหลังการอัปเดต

เอาท์พุต

สร้างทริกเกอร์แล้ว

ขั้นตอน 6) การอัปเดตมุมมองหลังจากแทนที่ทริกเกอร์ ตอนนี้ข้อผิดพลาดจะไม่เกิดขึ้นเนื่องจาก "แทนที่ทริกเกอร์" จะจัดการการดำเนินการอัปเดตของมุมมองที่ซับซ้อนนี้ และเมื่อโค้ดดำเนินการแล้ว ตำแหน่งของพนักงาน XXX จะถูกอัปเดตจาก "ญี่ปุ่น" เป็น "ฝรั่งเศส"

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; 
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

คำอธิบายรหัส:

• รหัสบรรทัด 49-53: อัปเดตตำแหน่งของ “XXX” เป็น 'FRANCE' ดำเนินการได้สำเร็จเนื่องจากทริกเกอร์ 'INSTEAD OF' ได้หยุดคำสั่งอัปเดตจริงในมุมมอง และดำเนินการอัปเดตตารางฐาน
• รหัสบรรทัด 55: การตรวจสอบบันทึกที่อัปเดต

Output:

ขั้นตอน PL/SQL เสร็จสมบูรณ์แล้ว

ชื่อพนักงาน	DEPT_NAME	สถานที่ตั้ง
ZZZ	HR	สหรัฐอเมริกา
YYY	ขาย	UK
XXX	การเงิน	ฝรั่งเศส

ทริกเกอร์แบบผสม

ทริกเกอร์แบบผสมคือทริกเกอร์ที่ให้คุณระบุการดำเนินการสำหรับแต่ละจุดเวลาทั้งสี่จุดในตัวทริกเกอร์เดี่ยว จุดกำหนดเวลาที่แตกต่างกันสี่จุดที่รองรับมีดังต่อไปนี้

• ก่อนแถลงการณ์ – ระดับ
• ก่อนแถว – ระดับ
• หลังแถว – ระดับ
• หลังจากแถลงการณ์ – ระดับ

ช่วยให้สามารถรวมการกระทำในช่วงเวลาที่แตกต่างกันเข้าไว้ในทริกเกอร์เดียวกันได้

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
ON <name of underlying object>
<Declarative part>‭	‬
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

คำอธิบายไวยากรณ์:

• ไวยากรณ์ข้างต้นแสดงการสร้างทริกเกอร์ 'COMPOUND'
• ส่วนการประกาศเป็นเรื่องปกติสำหรับบล็อกการดำเนินการทั้งหมดในเนื้อหาของทริกเกอร์
• ไทม์มิ่งบล็อคทั้ง 4 นี้สามารถอยู่ในลำดับใดก็ได้ ไม่จำเป็นต้องมีบล็อกเวลาทั้ง 4 รายการนี้ เราสามารถสร้างทริกเกอร์ COMPOUND สำหรับการกำหนดเวลาที่จำเป็นเท่านั้น

1 ตัวอย่าง: ในตัวอย่างนี้ เราจะสร้างทริกเกอร์เพื่อเติมคอลัมน์เงินเดือนโดยอัตโนมัติด้วยค่าเริ่มต้น 5000

CREATE TRIGGER emp_trig 
FOR INSERT 
ON emp
COMPOUND TRIGGER 
BEFORE EACH ROW IS 
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); 
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

คำอธิบายรหัส:

• รหัสบรรทัด 2-10: การสร้างทริกเกอร์แบบผสม มันถูกสร้างขึ้นสำหรับการกำหนดเวลาก่อนระดับแถวเพื่อเติมเงินเดือนด้วยค่าเริ่มต้น 5000 ซึ่งจะเปลี่ยนเงินเดือนเป็นค่าเริ่มต้น '5000' ก่อนที่จะแทรกบันทึกลงในตาราง
• รหัสบรรทัด 11-14: แทรกบันทึกลงในตาราง 'emp'
• รหัสบรรทัดที่ 16: ตรวจสอบบันทึกที่แทรก

Output:

สร้างทริกเกอร์แล้ว

ขั้นตอน PL/SQL เสร็จสมบูรณ์แล้ว

อีเอ็มพี_NAME	EMP_NO	เงินเดือน	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

การเปิดใช้งานและการปิดใช้งานทริกเกอร์

ทริกเกอร์สามารถเปิดหรือปิดใช้งานได้ หากต้องการเปิดใช้งานหรือปิดใช้งานทริกเกอร์ จำเป็นต้องกำหนดคำสั่ง ALTER (DDL) สำหรับทริกเกอร์ที่ปิดใช้งานหรือเปิดใช้งาน

ด้านล่างนี้คือไวยากรณ์สำหรับการเปิด/ปิดทริกเกอร์

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

คำอธิบายไวยากรณ์:

• ไวยากรณ์แรกแสดงวิธีเปิด/ปิดใช้งานทริกเกอร์เดี่ยว
• คำสั่งที่สองแสดงวิธีการเปิด/ปิดการใช้งานทริกเกอร์ทั้งหมดบนตารางใดตารางหนึ่ง

สรุป

ในบทนี้ เราได้เรียนรู้เกี่ยวกับทริกเกอร์ PL/SQL และข้อดีของมัน นอกจากนี้เรายังได้เรียนรู้การจำแนกประเภทต่างๆ และหารือเกี่ยวกับทริกเกอร์แทนและทริกเกอร์ COMPOUND