Triggers

PostgreSQL

Ahmad Yoosofan

Database course

University of Kashan, Spring 2020

Uses for triggers

• Enforce business rules
• Validate input data
• Generate a unique value for a newly-inserted row in a different file.
• Write to other files for audit trail purposes
• Query from other files for cross-referencing purposes
• Access system functions
• Replicate data to different files to achieve data consistency

Benefits of using triggers in business

• Faster application development. Because the database stores triggers, you do not have to code the trigger actions into each database application.
• Global enforcement of business rules. Define a trigger once and then reuse it for any application that uses the database.
• Easier maintenance. If a business policy changes, you need to change only the corresponding trigger program instead of each application program.
• Improve performance in client/server environment. All rules run on the server before the result returns.

Synopsis

 1 CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF }
 2     { event [ OR ... ] }    ON table_name
 3     [ FROM referenced_table_name ]
 4     [ NOT DEFERRABLE | [ DEFERRABLE ]
 5         [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ]
 6     ]
 7     [ REFERENCING { { OLD | NEW } TABLE [ AS ]
 8         transition_relation_name } [ ... ]
 9     ]
10     [ FOR [ EACH ] { ROW | STATEMENT } ]
11     [ WHEN ( condition ) ]
12     EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

• INSERT
• UPDATE [ OF column_name [, ... ] ]
• DELETE
• TRUNCATE

 1 CREATE OR REPLACE FUNCTION test()
 2   RETURNS trigger AS
 3 $$
 4 BEGIN
 5   INSERT INTO test_table(col1, col2, col3)
 6   VALUES(NEW.col1, NEW.col2, current_date);
 7 
 8   RETURN NEW;
 9 END;
10 $$
11 LANGUAGE 'plpgsql';

1 CREATE TRIGGER test_trigger
2   AFTER INSERT
3   ON test_table
4   FOR EACH ROW
5   EXECUTE PROCEDURE test();

1 INSERT INTO "test_table"("col1", "col2") VALUES("A", "B");

 1 CREATE OR REPLACE FUNCTION rec_insert()
 2 RETURNS trigger AS
 3 $$
 4 BEGIN
 5   INSERT INTO emp_log(emp_id,salary,edittime)
 6   VALUES(NEW.employee_id, NEW.salary, current_date);
 7   RETURN NEW;
 8 END;
 9 $$
10 LANGUAGE 'plpgsql';

1 CREATE TRIGGER ins_same_rec
2   AFTER INSERT
3   ON emp_details
4   FOR EACH ROW
5   EXECUTE PROCEDURE rec_insert();

1 INSERT INTO emp_details VALUES(236, 'RABI', 'CHANDRA', 'RABI',
2 '590.423.45700', '2013-01-12', 'AD_VP', 15000, .5);

1 CREATE OR REPLACE FUNCTION befo_insert()
2   RETURNS trigger AS $$
3 BEGIN
4 NEW.FIRST_NAME = LTRIM(NEW.FIRST_NAME);
5 NEW.LAST_NAME = LTRIM(NEW.LAST_NAME);
6 NEW.JOB_ID = UPPER(NEW.JOB_ID);
7 RETURN NEW;
8 END; $$ LANGUAGE 'plpgsql';

1 CREATE TRIGGER che_val_befo_ins
2   BEFORE INSERT
3   ON emp_details
4   FOR EACH ROW
5   EXECUTE PROCEDURE befo_insert();

1 INSERT INTO emp_details VALUES (334, ' Ana ', ' King', 'ANA',
2 '690.432.45701', '2013-02-05', 'it_prog', 17000, .50);

postgres=# SELECT * FROM student_mast;
 student_id |           name            | st_class
------------+---------------------------+----------
          1 | Steven King               |        7
          2 | Neena  Kochhar            |        8
          3 | Lex  De Haan              |        8
          4 | Alexander Hunold          |       10
(4 rows)

• stu_log table has two columns user_id and description

1 CREATE OR REPLACE FUNCTION aft_update()
2   RETURNS trigger AS   $$
3 BEGIN
4 INSERT into stu_log VALUES (user, CONCAT('Update ',
5          OLD.NAME,' Previous:',OLD.ST_CLASS,' Present: ',
6          NEW.st_class));
7 RETURN NEW;
8 END; $$  LANGUAGE 'plpgsql';

1 CREATE TRIGGER updt_log
2   AFTER UPDATE
3   ON student_mast
4   FOR EACH ROW
5   EXECUTE PROCEDURE aft_update();

1 UPDATE STUDENT_MAST SET ST_CLASS = ST_CLASS + 1;

postgres=# SELECT * FROM student_mast;
 student_id |           name            | st_class
------------+---------------------------+----------
          1 | Steven King               |        8
          2 | Neena  Kochhar            |        9
          3 | Lex  De Haan              |        9
          4 | Alexander Hunold          |       11
(4 rows)

postgres=# select * from stu_log;
user_id |            description
--------+---------------------------------------------------
postgres| Update Steven King       Previous:7 Present 8
postgres| Update Neena  Kochhar    Previous:8 Present 9
postgres| Update Lex  De Haan      Previous:8 Present 9
postgres| Update Alexander Hunold  Previous:10 Present 11
(4 rows)

 1 CREATE OR REPLACE FUNCTION befo_update()
 2   RETURNS trigger AS $$
 3 BEGIN
 4 NEW.TOTAL = NEW.SUB1 + NEW.SUB2 + NEW.SUB3 + NEW.SUB4 + NEW.SUB5;
 5 NEW.PER_MARKS = NEW.TOTAL/5;
 6 IF NEW.PER_MARKS >=90 THEN
 7 NEW.GRADE = 'EXCELLENT';
 8 ELSEIF NEW.PER_MARKS>=75 AND NEW.PER_MARKS<90 THEN
 9 NEW.GRADE = 'VERY GOOD';
10 ELSEIF NEW.PER_MARKS>=60 AND NEW.PER_MARKS<75 THEN
11 NEW.GRADE = 'GOOD';
12 ELSEIF NEW.PER_MARKS>=40 AND NEW.PER_MARKS<60 THEN
13 NEW.GRADE = 'AVERAGE';
14 ELSE
15 NEW.GRADE = 'NOT PROMOTED';
16 END IF;
17 RETURN NEW;
18 END;
19 $$ LANGUAGE 'plpgsql';

1 CREATE TRIGGER updt_marks
2   BEFORE UPDATE
3   ON student_marks
4   FOR EACH ROW
5   EXECUTE PROCEDURE befo_update();

postgres=# SELECT * FROM STUDENT_MARKS;
 student_id |           name            | sub1 | sub2 | sub3 | sub4 | sub5 | total | per_marks |        grade
------------+---------------------------+------+------+------+------+------+-------+-----------+----------------------
          2 | Neena  Kochhar            |      |      |      |      |      |       |           |
          3 | Lex  De Haan              |      |      |      |      |      |       |           |
          4 | Alexander Hunold          |      |      |      |      |      |       |           |
          1 | Steven King               |   54 |   69 |   89 |   87 |   59 |   358 |        71 | GOOD
(4 rows)

1 CREATE OR REPLACE FUNCTION aft_delete()
2   RETURNS trigger AS $$
3 BEGIN
4 INSERT into stu_log VALUES (user, CONCAT('Update Student Record ',
5          OLD.NAME,' Class :',OLD.ST_CLASS,' -> Deleted on ',
6          NOW()));
7 RETURN NEW;
8 END;  $$ LANGUAGE 'plpgsql';

1 CREATE TRIGGER delete_stu
2   AFTER DELETE
3   ON student_mast
4   FOR EACH ROW
5   EXECUTE PROCEDURE aft_delete();

postgres=# SELECT * FROM STUDENT_MAST;
 student_id |           name            | st_class
------------+---------------------------+----------
          2 | Neena  Kochhar            |        9
          3 | Lex  De Haan              |        9
          4 | Alexander Hunold          |       11
(3 rows)

postgres=# select * from stu_log;
          user_id          |                                             description
---------------------------+------------------------------------------------------------------------------------------------------
 postgres                  | Update Student Record Steven King               Previous Class :7 Present Class 8
 postgres                  | Update Student Record Neena  Kochhar            Previous Class :8 Present Class 9
 postgres                  | Update Student Record Lex  De Haan              Previous Class :8 Present Class 9
 postgres                  | Update Student Record Alexander Hunold          Previous Class :10 Present Class 11
 postgres                  | Update Student Record Steven King               Class :7 -> Deleted on 2014-09-16 16:30:35.093+05:30
(5 rows)

1 DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

1 DROP TRIGGER delete_stu on student_mast;

Related

• https://w3resource.com/PostgreSQL/postgresql-triggers.php
• https://www.postgresql.org/docs/current/sql-createtrigger.html