PostgreSQL Triggers

Introduction on Triggers

A trigger is a set of actions that are run automatically when a specified change operation (SQL INSERT, UPDATE, DELETE or TRUNCATE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.

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.

Implementation of SQL triggers is based on the SQL standard. It supports constructs that are common to most programming languages. It supports the declaration of local variables, statements to control the flow of the procedure, assignment of expression results to variables, and error handling.

PostgreSQL: Create trigger

A trigger is a named database object that is associated with a table, and it activates when a particular event (e.g. an insert, update or delete) occurs for the table/views. The statement CREATE TRIGGER creates a new trigger in PostgreSQL. Here is the syntax :

Syntax:

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

Parameters

Triggers that are specified to fire INSTEAD OF the trigger event must be marked FOR EACH ROW, and can only be defined on views. BEFORE and AFTER triggers on a view must be marked as FOR EACH STATEMENT. In addition, triggers may be defined to fire for TRUNCATE, though only FOR EACH STATEMENT. The following table summarizes which types of triggers may be used on tables and views:

Here is a simple example of trigger function.:

Code:

CREATE OR REPLACE FUNCTION test()
  RETURNS trigger AS
$$
BEGIN
         INSERT INTO test_table(col1,col2,col3)
         VALUES(NEW.col1,NEW.col2,current_date);
 
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

Now we can create the trigger which will fire at the time of execution the event as specified in the trigger for the associated tables.

Code:

CREATE TRIGGER test_trigger
  AFTER INSERT
  ON test_table
  FOR EACH ROW
  EXECUTE PROCEDURE test();
  

In the above trigger function there is new keyword 'NEW' which is a PostgreSQL extension to triggers. There are two PostgreSQL extensions to trigger 'OLD' and 'NEW'. OLD and NEW are not case sensitive.

• Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger
• In an INSERT trigger, only NEW.col_name can be used.
• In a UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.
• In a DELETE trigger, only OLD.col_name can be used; there is no new row.

A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)

Sample database, table, table structure, table records for various examples

Records of the table (on some fields): emp_details

postgres=#  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details;
 employee_id | first_name  | last_name |   job_id   |  salary  | commission_pct
-------------+-------------+-----------+------------+----------+----------------
         100 | Steven      | King      | AD_PRES    | 24000.00 |           0.00
         101 | Neena       | Kochhar   | AD_VP      | 17000.00 |           0.00
         102 | Lex         | De Haan   | AD_VP      | 17000.00 |           0.00
         103 | Alexander   | Hunold    | IT_PROG    |  9000.00 |           0.00
         104 | Bruce       | Ernst     | IT_PROG    |  6000.00 |           0.00
         105 | David       | Austin    | IT_PROG    |  4800.00 |           0.00
         106 | Valli       | Pataballa | IT_PROG    |  4800.00 |           0.00
         107 | Diana       | Lorentz   | IT_PROG    |  4200.00 |           0.00
         108 | Nancy       | Greenberg | FI_MGR     | 12000.00 |           0.00
         109 | Daniel      | Faviet    | FI_ACCOUNT |  9000.00 |           0.00
         110 | John        | Chen      | FI_ACCOUNT |  8200.00 |           0.00
         111 | Ismael      | Sciarra   | FI_ACCOUNT |  7700.00 |           0.00
         112 | Jose Manuel | Urman     | FI_ACCOUNT |  7800.00 |           0.00
(13 rows)

PostgreSQL Trigger: Example AFTER INSERT

In the following example we have two tables : emp_details and emp_log. To insert some information into emp_logs table (which have three fields emp_id and salary and edttime) every time, when an INSERT happen into emp_details table we have used the following trigger :

At first a trigger function have to create. Here is the trigger function rec_insert()

Code:

CREATE OR REPLACE FUNCTION rec_insert()
  RETURNS trigger AS
$$
BEGIN
         INSERT INTO emp_log(emp_id,salary,edittime)
         VALUES(NEW.employee_id,NEW.salary,current_date);
 
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

Here is the trigger ins_same_rec:

Code:

CREATE TRIGGER ins_same_rec
  AFTER INSERT
  ON emp_details
  FOR EACH ROW
  EXECUTE PROCEDURE rec_insert();
  

Records of the table (on some columns): emp_details

postgres=#  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details;

employee_id | first_name  | last_name |   job_id   |  salary  | commission_pct
-------------+-------------+-----------+------------+----------+----------------
         100 | Steven      | King      | AD_PRES    | 24000.00 |           0.00
         101 | Neena       | Kochhar   | AD_VP      | 17000.00 |           0.00
         102 | Lex         | De Haan   | AD_VP      | 17000.00 |           0.00
         103 | Alexander   | Hunold    | IT_PROG    |  9000.00 |           0.00
         104 | Bruce       | Ernst     | IT_PROG    |  6000.00 |           0.00
         105 | David       | Austin    | IT_PROG    |  4800.00 |           0.00
         106 | Valli       | Pataballa | IT_PROG    |  4800.00 |           0.00
         107 | Diana       | Lorentz   | IT_PROG    |  4200.00 |           0.00
         108 | Nancy       | Greenberg | FI_MGR     | 12000.00 |           0.00
         109 | Daniel      | Faviet    | FI_ACCOUNT |  9000.00 |           0.00
         110 | John        | Chen      | FI_ACCOUNT |  8200.00 |           0.00
         111 | Ismael      | Sciarra   | FI_ACCOUNT |  7700.00 |           0.00
         112 | Jose Manuel | Urman     | FI_ACCOUNT |  7800.00 |           0.00
(13 rows)

Records of the table (all columns): emp_log

postgres=# SELECT * FROM emp_log;
 emp_id | salary |  edittime
--------+--------+------------
    100 |  24000 | 2011-01-15
    101 |  17000 | 2010-01-12
    102 |  17000 | 2010-09-22
    103 |   9000 | 2011-06-21
    104 |   6000 | 2012-07-05
    105 |   4800 | 2011-06-02
(6 rows)

Now insert one record in emp_details table see the records both in emp_details and emp_log tables :

Code:

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

postgres=#  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details;
 employee_id | first_name  | last_name |   job_id   |  salary  | commission_pct
-------------+-------------+-----------+------------+----------+----------------
         100 | Steven      | King      | AD_PRES    | 24000.00 |           0.00
         101 | Neena       | Kochhar   | AD_VP      | 17000.00 |           0.00
         102 | Lex         | De Haan   | AD_VP      | 17000.00 |           0.00
         103 | Alexander   | Hunold    | IT_PROG    |  9000.00 |           0.00
         104 | Bruce       | Ernst     | IT_PROG    |  6000.00 |           0.00
         105 | David       | Austin    | IT_PROG    |  4800.00 |           0.00
         106 | Valli       | Pataballa | IT_PROG    |  4800.00 |           0.00
         107 | Diana       | Lorentz   | IT_PROG    |  4200.00 |           0.00
         108 | Nancy       | Greenberg | FI_MGR     | 12000.00 |           0.00
         109 | Daniel      | Faviet    | FI_ACCOUNT |  9000.00 |           0.00
         110 | John        | Chen      | FI_ACCOUNT |  8200.00 |           0.00
         111 | Ismael      | Sciarra   | FI_ACCOUNT |  7700.00 |           0.00
         112 | Jose Manuel | Urman     | FI_ACCOUNT |  7800.00 |           0.00
         236 | RABI        | CHANDRA   | AD_VP      | 15000.00 |           0.50
(14 rows)

postgres=#  SELECT * FROM emp_log;
 emp_id | salary |  edittime
--------+--------+------------
    100 |  24000 | 2011-01-15
    101 |  17000 | 2010-01-12
    102 |  17000 | 2010-09-22
    103 |   9000 | 2011-06-21
    104 |   6000 | 2012-07-05
    105 |   4800 | 2011-06-02
    236 |  15000 | 2014-09-15
(7 rows)

PostgreSQL Trigger: Example BEFORE INSERT

In the following example, before insert a new record in emp_details table, a trigger check the column value of FIRST_NAME, LAST_NAME, JOB_ID and
- If there are any space(s) before or after the FIRST_NAME, LAST_NAME, LTRIM() function will remove those.
- The value of the JOB_ID will be converted to upper cases by UPPER() function.
Here is the trigger function befo_insert():

Code:

CREATE OR REPLACE FUNCTION befo_insert()
  RETURNS trigger AS
$$
BEGIN
NEW.FIRST_NAME = LTRIM(NEW.FIRST_NAME);
NEW.LAST_NAME = LTRIM(NEW.LAST_NAME);
NEW.JOB_ID = UPPER(NEW.JOB_ID);
RETURN NEW;
END;

$$
LANGUAGE 'plpgsql';

Code:

CREATE TRIGGER che_val_befo_ins
  BEFORE INSERT
  ON emp_details
  FOR EACH ROW
  EXECUTE PROCEDURE befo_insert();
  

Now insert a row into emp_details table (check the FIRST_NAME, LAST_NAME, JOB_ID columns):

Code:

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

Now list the following fields of emp_details :

postgres=#   SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details;
 employee_id | first_name  | last_name |   job_id   |  salary  | commission_pct
-------------+-------------+-----------+------------+----------+----------------
         100 | Steven      | King      | AD_PRES    | 24000.00 |           0.00
         101 | Neena       | Kochhar   | AD_VP      | 17000.00 |           0.00
         102 | Lex         | De Haan   | AD_VP      | 17000.00 |           0.00
         103 | Alexander   | Hunold    | IT_PROG    |  9000.00 |           0.00
         104 | Bruce       | Ernst     | IT_PROG    |  6000.00 |           0.00
         105 | David       | Austin    | IT_PROG    |  4800.00 |           0.00
         106 | Valli       | Pataballa | IT_PROG    |  4800.00 |           0.00
         107 | Diana       | Lorentz   | IT_PROG    |  4200.00 |           0.00
         108 | Nancy       | Greenberg | FI_MGR     | 12000.00 |           0.00
         109 | Daniel      | Faviet    | FI_ACCOUNT |  9000.00 |           0.00
         110 | John        | Chen      | FI_ACCOUNT |  8200.00 |           0.00
         111 | Ismael      | Sciarra   | FI_ACCOUNT |  7700.00 |           0.00
         112 | Jose Manuel | Urman     | FI_ACCOUNT |  7800.00 |           0.00
         236 | RABI        | CHANDRA   | AD_VP      | 15000.00 |           0.50
         334 | Ana         | King      | IT_PROG    | 17000.00 |           0.50
(15 rows)

See the last row:

FIRST_NAME - > ' Ana '  has changed to 'Ana' 
LAST_NAME - > ' King'  has changed to 'King'
JOB_ID - > ' it_prog'  has changed to 'IT_PROG' 

PostgreSQL Trigger: Example AFTER UPDATE

We have two tables student_mast and stu_log. student_mast have three columns STUDENT_ID, NAME, ST_CLASS. stu_log table has two columns user_id and description.

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)

Let we promote all the students in next class i.e. 7 will be 8, 8 will be 9 and so on. After updating a single row in student_mast table a new row will be inserted in stu_log table where we will store the current user id and a small description regarding the current update. Here is the trigger code:

Code:

CREATE OR REPLACE FUNCTION aft_update()
  RETURNS trigger AS
$$
BEGIN
INSERT into stu_log VALUES (user, CONCAT('Update Student Record ',
         OLD.NAME,' Previous Class :',OLD.ST_CLASS,' Present Class ',
         NEW.st_class));
RETURN NEW;
END;

$$
LANGUAGE 'plpgsql';

Here is the trigger for that event-

Code:

CREATE TRIGGER updt_log
  AFTER UPDATE
  ON student_mast
  FOR EACH ROW
  EXECUTE PROCEDURE aft_update();
  

Now update the student_mast table:

Code:

UPDATE STUDENT_MAST SET ST_CLASS = ST_CLASS + 1;

The trigger shows you the updated records in 'stu_log'. Here is the latest position of STUDENT_MAST and STU_LOG tables :

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 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
(4 rows)

PostgreSQL Trigger: Example BEFORE UPDATE

We have a table student_marks with 10 columns and 4 rows. There are data only in STUDENT_ID and NAME columns.

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

Now the exam is over and we have received all subject marks, now we will update the table, total marks of all subject, the percentage of total marks and grade will be automatically calculated. For this sample calculation, the following conditions are assumed:

Total Marks (will be stored in TOTAL column): TOTAL = SUB1 + SUB2 + SUB3 + SUB4 + SUB5

Percentage of Marks (will be stored in PER_MARKS column) : PER_MARKS = (TOTAL)/5

Grade (will be stored GRADE column):

- If PER_MARKS>=90 -> 'EXCELLENT'

- If PER_MARKS>=75 AND PER_MARKS<90 -> 'VERY GOOD'

- If PER_MARKS>=60 AND PER_MARKS<75 -> 'GOOD'

- If PER_MARKS>=40 AND PER_MARKS<60 -> 'AVERAGE'

- If PER_MARKS<40-> 'NOT PROMOTED'

Here is the code:

Code:

UPDATE STUDENT_MARKS SET SUB1 = 54, SUB2 = 69, SUB3 = 89, SUB4 = 87,
SUB5 = 59 WHERE STUDENT_ID = 1;

Let update the marks of a student:

Here is the trigger function befo_update:

Code:

CREATE OR REPLACE FUNCTION befo_update()
  RETURNS trigger AS
$$
BEGIN
NEW.TOTAL = NEW.SUB1 + NEW.SUB2 + NEW.SUB3 + NEW.SUB4 + NEW.SUB5; 
NEW.PER_MARKS = NEW.TOTAL/5;
IF NEW.PER_MARKS >=90 THEN
NEW.GRADE = 'EXCELLENT';
ELSEIF NEW.PER_MARKS>=75 AND NEW.PER_MARKS<90 THEN
NEW.GRADE = 'VERY GOOD';
ELSEIF NEW.PER_MARKS>=60 AND NEW.PER_MARKS<75 THEN
NEW.GRADE = 'GOOD';
ELSEIF NEW.PER_MARKS>=40 AND NEW.PER_MARKS<60 THEN
NEW.GRADE = 'AVERAGE';
ELSE
NEW.GRADE = 'NOT PROMOTED';
END IF;

RETURN NEW;
END;

$$
LANGUAGE 'plpgsql';

Here is the trigger

Code:

CREATE TRIGGER updt_marks
  BEFORE UPDATE
  ON student_marks
  FOR EACH ROW
  EXECUTE PROCEDURE befo_update();
  

Now check the STUDENT_MARKS table with updated data. The trigger show you the updated records in 'stu_log'.

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)

PostgreSQL Trigger: Example AFTER DELETE

In our 'AFTER UPDATE' example we had two tables student_mast and stu_log. student_mast have three columns STUDENT_ID, NAME, ST_CLASS and stu_log table has two columns user_id and description. We want to store some information in stu_log table after a delete operation happened on student_mast table. Here is the trigger:

Code:

CREATE OR REPLACE FUNCTION aft_delete()
  RETURNS trigger AS
$$
BEGIN
INSERT into stu_log VALUES (user, CONCAT('Update Student Record ',
         OLD.NAME,' Class :',OLD.ST_CLASS,' -> Deleted on ',
         NOW()));
RETURN NEW;
END;

$$
LANGUAGE 'plpgsql';

Here is the trigger

Code:

CREATE TRIGGER delete_stu
  AFTER DELETE
  ON student_mast
  FOR EACH ROW
  EXECUTE PROCEDURE aft_delete();
  

Let delete a student from student_mast

Code:

DELETE FROM STUDENT_MAST WHERE STUDENT_ID = 1;

Here is the latest position of student_mast, stu_log tables :

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)

DROP a PostgreSQL trigger

To delete or destroy a trigger, use a DROP TRIGGER statement. To execute this command, the current user must be the owner of the table for which the trigger is defined.

Syntax

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

Parameters

Example:

If you delete or drop the just created trigger delete_stu the following statement can be used:

Code:

DROP TRIGGER delete_stu on student_mast;

The trigger delete_stu will be deleted.

Previous: VIEWS
Next: Intorduction to plpgSQL