PostgreSQL - ทริกเกอร์

PostgreSQL Triggers คือฟังก์ชันเรียกกลับฐานข้อมูลซึ่งจะดำเนินการ / เรียกใช้โดยอัตโนมัติเมื่อเกิดเหตุการณ์ฐานข้อมูลที่ระบุ

ต่อไปนี้เป็นประเด็นสำคัญเกี่ยวกับทริกเกอร์ PostgreSQL -

• สามารถระบุทริกเกอร์ PostgreSQL เพื่อเริ่มการทำงาน

  • ก่อนที่จะมีการพยายามดำเนินการในแถว (ก่อนที่จะตรวจสอบข้อ จำกัด และพยายาม INSERT, UPDATE หรือ DELETE)

  • หลังจากการดำเนินการเสร็จสิ้น (หลังจากตรวจสอบข้อ จำกัด และ INSERT, UPDATE หรือ DELETE เสร็จสิ้น)

  • แทนการดำเนินการ (ในกรณีของการแทรกอัพเดตหรือลบในมุมมอง)

• ทริกเกอร์ที่ถูกทำเครื่องหมายสำหรับแต่ละแถวจะถูกเรียกหนึ่งครั้งสำหรับทุกแถวที่การดำเนินการแก้ไข ในทางตรงกันข้ามทริกเกอร์ที่ถูกทำเครื่องหมายสำหรับแต่ละ STATEMENT จะดำเนินการเพียงครั้งเดียวสำหรับการดำเนินการใด ๆ ก็ตามไม่ว่าจะแก้ไขกี่แถวก็ตาม

• ทั้งคำสั่ง WHEN และการดำเนินการทริกเกอร์อาจเข้าถึงองค์ประกอบของแถวที่กำลังแทรกลบหรืออัปเดตโดยใช้การอ้างอิงของแบบฟอร์ม NEW.column-name และ OLD.column-nameโดยที่ชื่อคอลัมน์คือชื่อของคอลัมน์จากตารางที่เกี่ยวข้องกับทริกเกอร์

• หากมีการระบุคำสั่ง WHEN คำสั่ง PostgreSQL ที่ระบุจะถูกเรียกใช้เฉพาะสำหรับแถวที่ส่วนคำสั่ง WHEN เป็นจริง หากไม่มีระบุคำสั่ง WHEN คำสั่ง PostgreSQL จะดำเนินการสำหรับทุกแถว

• หากมีการกำหนดทริกเกอร์ชนิดเดียวกันหลายตัวสำหรับเหตุการณ์เดียวกันทริกเกอร์จะเริ่มทำงานตามลำดับตัวอักษรตามชื่อ

• คำหลักก่อนหลังหรือแทนคำหลักกำหนดว่าจะดำเนินการทริกเกอร์เมื่อใดโดยสัมพันธ์กับการแทรกการแก้ไขหรือการลบแถวที่เกี่ยวข้อง

• ทริกเกอร์จะหลุดโดยอัตโนมัติเมื่อตารางที่เกี่ยวข้องถูกทิ้ง

• ตารางที่จะแก้ไขต้องมีอยู่ในฐานข้อมูลเดียวกับตารางหรือมุมมองที่แนบทริกเกอร์และต้องใช้เพียง tablenameไม่ใช่ database.tablename.

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

ไวยากรณ์

ไวยากรณ์พื้นฐานของการสร้างไฟล์ trigger มีดังนี้ -

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];

ที่นี่ event_nameอาจเป็นการดำเนินการINSERT, DELETE, UPDATEและTRUNCATEในตารางที่กล่าวถึงtable_name. คุณสามารถเลือกระบุสำหรับแต่ละแถวหลังชื่อตารางได้

ต่อไปนี้เป็นไวยากรณ์ของการสร้างทริกเกอร์ในการดำเนินการ UPDATE ในคอลัมน์ที่ระบุอย่างน้อยหนึ่งคอลัมน์ของตารางดังนี้ -

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic goes here....
];

ตัวอย่าง

ให้เราพิจารณากรณีที่เราต้องการให้มีการทดลองตรวจสอบสำหรับทุกระเบียนที่แทรกในตาราง COMPANY ซึ่งเราจะสร้างขึ้นใหม่ดังต่อไปนี้ (วางตาราง COMPANY หากคุณมีอยู่แล้ว)

testdb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

เพื่อทำการทดลองตรวจสอบต่อไปเราจะสร้างตารางใหม่ชื่อ AUDIT ซึ่งจะแทรกข้อความบันทึกเมื่อใดก็ตามที่มีรายการในตาราง COMPANY สำหรับบันทึกใหม่ -

testdb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

ในที่นี้ ID คือ ID เร็กคอร์ด AUDIT และ EMP_ID คือ ID ซึ่งจะมาจากตาราง COMPANY และ DATE จะเก็บการประทับเวลาเมื่อสร้างเรกคอร์ดในตาราง COMPANY ตอนนี้ให้เราสร้างทริกเกอร์ในตาราง COMPANY ดังนี้ -

testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

โดย auditlogfunc () คือ PostgreSQL procedure และมีคำจำกัดความดังต่อไปนี้ -

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

ตอนนี้เราจะเริ่มงานจริง ให้เราเริ่มแทรกบันทึกในตาราง COMPANY ซึ่งจะส่งผลให้สร้างบันทึกบันทึกการตรวจสอบในตาราง AUDIT ดังนั้นให้เราสร้างหนึ่งระเบียนในตาราง COMPANY ดังนี้ -

testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

สิ่งนี้จะสร้างหนึ่งระเบียนในตาราง COMPANY ซึ่งมีดังต่อไปนี้ -

id | name | age | address      | salary
----+------+-----+--------------+--------
  1 | Paul |  32 | California   |  20000

ในเวลาเดียวกันจะมีการสร้างระเบียนหนึ่งรายการในตาราง AUDIT บันทึกนี้เป็นผลมาจากทริกเกอร์ซึ่งเราได้สร้างขึ้นในการดำเนินการ INSERT บนตาราง COMPANY ในทำนองเดียวกันคุณสามารถสร้างทริกเกอร์ของคุณในการอัปเดตและลบการดำเนินการตามความต้องการของคุณ

emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

รายชื่อทริกเกอร์

คุณสามารถแสดงรายการทริกเกอร์ทั้งหมดในฐานข้อมูลปัจจุบันได้จาก pg_trigger ตารางดังนี้ -

testdb=# SELECT * FROM pg_trigger;

คำสั่ง PostgreSQL ข้างต้นจะแสดงรายการทริกเกอร์ทั้งหมด

หากคุณต้องการแสดงรายการทริกเกอร์บนตารางใดตารางหนึ่งให้ใช้ AND อนุประโยคกับชื่อตารางดังนี้ -

testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

คำสั่ง PostgreSQL ข้างต้นจะแสดงรายการเพียงรายการเดียวดังนี้ -

tgname
-----------------
 example_trigger
(1 row)

การทิ้งทริกเกอร์

ต่อไปนี้คือคำสั่ง DROP ซึ่งสามารถใช้เพื่อวางทริกเกอร์ที่มีอยู่ -

testdb=# DROP TRIGGER trigger_name;