Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How To Create PostgreSQL PL/pgSQL Trigger Procedures

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
Name
Description
name
The name of the trigger. A trigger  must be distinct from the name of any other trigger for the same table. The name cannot be schema-qualified — the trigger inherits the schema of its table. 
BEFORE
AFTER
INSTEAD OF
Determines whether the function is called before, after, or instead of the event. A constraint trigger can only be specified as AFTER.
event
One of INSERT, UPDATE, DELETE, or TRUNCATE, that will fire the trigger.
table_name
The name of the table or view the trigger is for.
referenced_table_name
The (possibly schema-qualified) name of another table referenced by the constraint. This option is used for foreign-key constraints and is not recommended for general use. This can only be specified for constraint triggers.
DEFERRABLE NOT
DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED
The default timing of the trigger.
FOR EACH ROW
FOR EACH STATEMENT
Specifies whether the trigger procedure should be fired once for every row affected by the trigger event, or just once per SQL statement. If neither is specified, FOR EACH STATEMENT is the default.
condition
A Boolean expression that determines whether the trigger function will actually be executed.
function_name
A user-supplied function that is declared as taking no arguments and returning type trigger, which is executed when the trigger fires.
arguments
An optional comma-separated list of arguments to be provided to the function when the trigger is executed. The arguments are literal string constants.
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:
When
Event
Row-level
Statement-level
BEFORE
INSERT/UPDATE/DELETE
Tables
Tables and views
TRUNCATE
Tables
AFTER
INSERT/UPDATE/DELETE
Tables
Tables and views
TRUNCATE
Tables
INSTEAD OF
INSERT/UPDATE/DELETE
Views
TRUNCATE
Here is a simple example of trigger function.:
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.
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()
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:
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 :
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() :
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';    
Here is the trigger che_val_befo_ins:
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) :
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 :
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-
CREATE TRIGGER updt_log  
  AFTER UPDATE  
  ON student_mast  
  FOR EACH ROW  
  EXECUTE PROCEDURE aft_update();    
Now update the student_mast table :
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 :
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:
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
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 :
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
CREATE TRIGGER delete_stu  
  AFTER DELETE  
  ON student_mast  
  FOR EACH ROW  
  EXECUTE PROCEDURE aft_delete();    
Let delete a student from student_mast
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
Name
Description
IF EXISTS
Do not throw an error if the trigger does not exist. A notice is issued in this case.
name
The name of the trigger to remove.
table_name
The name (optionally schema-qualified) of the table for which the trigger is defined.
CASCADE
Automatically drop objects that depend on the trigger.
RESTRICT
Refuse to drop the trigger if any objects depend on it. This is the default.
Example:
If you delete or drop the just created trigger delete_stu the following statement can be used:
DROP TRIGGER delete_stu on student_mast;
The trigger delete_stu will be deleted.

PL/pgSQL Trigger Procedure
This example trigger ensures that any time a row is inserted or updated in the table, the current user name and time are stamped into the row. And it checks that an employee's name is given and that the salary is a positive value.
CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when they must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
Another way to log changes to a table involves creating a new table that holds a row for each insert, update, or delete that occurs. This approach can be thought of as auditing changes to a table.

 PL/pgSQL Trigger Procedure For Auditing
This example trigger ensures that any insert, update or delete of a row in the emp table is recorded (i.e., audited) in the emp_audit table. The current time and user name are stamped into the row, together with the type of operation performed on it.
CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
A variation of the previous example uses a view joining the main table to the audit table, to show when each entry was last modified. This approach still records the full audit trail of changes to the table, but also presents a simplified view of the audit trail, showing just the last modified timestamp derived from the audit trail for each entry. 

PL/pgSQL View Trigger Procedure For Auditing
This example uses a trigger on the view to make it updatable, and ensure that any insert, update or delete of a row in the view is recorded (i.e., audited) in the emp_audit table. The current time and user name are recorded, together with the type of operation performed, and the view displays the last modified time of each row.
CREATE TABLE emp (
    empname           text PRIMARY KEY,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary            integer,
    stamp             timestamp NOT NULL
);

CREATE VIEW emp_view AS
    SELECT e.empname,
           e.salary,
           max(ea.stamp) AS last_updated
      FROM emp e
      LEFT JOIN emp_audit ea ON ea.empname = e.empname
     GROUP BY 1, 2;

CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
    BEGIN
        --
        -- Perform the required operation on emp, and create a row in emp_audit
        -- to reflect the change made to emp.
        --
        IF (TG_OP = 'DELETE') THEN
            DELETE FROM emp WHERE empname = OLD.empname;
            IF NOT FOUND THEN RETURN NULL; END IF;

            OLD.last_updated = now();
            INSERT INTO emp_audit VALUES('D', user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
            IF NOT FOUND THEN RETURN NULL; END IF;

            NEW.last_updated = now();
            INSERT INTO emp_audit VALUES('U', user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp VALUES(NEW.empname, NEW.salary);

            NEW.last_updated = now();
            INSERT INTO emp_audit VALUES('I', user, NEW.*);
            RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
    FOR EACH ROW EXECUTE PROCEDURE update_emp_view();
One use of triggers is to maintain a summary table of another table. The resulting summary can be used in place of the original table for certain queries — often with vastly reduced run times. This technique is commonly used in Data Warehousing, where the tables of measured or observed data (called fact tables) might be extremely large. 

PL/pgSQL Trigger Procedure For Maintaining A Summary Table
The schema detailed here is partly based on the Grocery Store example from The Data Warehouse Toolkit by Ralph Kimball.
--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

        -- Work out the increment/decrement amount(s).
        IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- forbid updates that change the time_key -
            -- (probably not too onerous, as DELETE + INSERT is how most
            -- changes will be made).
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
                                                      OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        END IF;


        -- Insert or update the summary row with the new values.
        <<insert_update>>
        LOOP
            UPDATE sales_summary_bytime
                SET amount_sold = amount_sold + delta_amount_sold,
                    units_sold = units_sold + delta_units_sold,
                    amount_cost = amount_cost + delta_amount_cost
                WHERE time_key = delta_time_key;

            EXIT insert_update WHEN found;

            BEGIN
                INSERT INTO sales_summary_bytime (
                            time_key,
                            amount_sold,
                            units_sold,
                            amount_cost)
                    VALUES (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );

                EXIT insert_update;

            EXCEPTION
                WHEN UNIQUE_VIOLATION THEN
                    -- do nothing
            END;
        END LOOP insert_update;

        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();

INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;

Triggers on Events

PL/pgSQL can be used to define event triggers. PostgreSQL requires that a procedure that is to be called as an event trigger must be declared as a function with no arguments and a return type of event_trigger.
When a PL/pgSQL function is called as an event trigger, several special variables are created automatically in the top-level block. They are:
TG_EVENT
Data type text; a string representing the event the trigger is fired for.
TG_TAG
Data type text; variable that contains the command tag for which the trigger is fired.
 shows an example of an event trigger procedure in PL/pgSQL.

PL/pgSQL Event Trigger Procedure
This example trigger simply raises a NOTICE message each time a supported command is executed.
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE PROCEDURE snitch();
Display list:
  1. Functions: \df+
  2. Triggers : \dy+
Display Definition:
postgres=# \sf
function name is required

postgres=# \sf pg_reload_conf()
CREATE OR REPLACE FUNCTION pg_catalog.pg_reload_conf()
 RETURNS boolean
 LANGUAGE internal
 STRICT
AS $function$pg_reload_conf$function$

postgres=# \sf pg_encoding_to_char
CREATE OR REPLACE FUNCTION pg_catalog.pg_encoding_to_char(integer)
 RETURNS name
 LANGUAGE internal
 STABLE STRICT
AS $function$PG_encoding_to_char$function$
For function:
you can query the pg_proc view , just as the following
select proname,prosrc from pg_proc where proname= your_function_name; 
Another way is that just execute the commont \df and \ef which can list the functions.
skytf=> \df           
                                             List of functions
 Schema |         Name         | Result data type |              Argument data types               |  Type  
--------+----------------------+------------------+------------------------------------------------+--------
 public | pg_buffercache_pages | SETOF record     |                                                | normal


skytf=> \ef  pg_buffercache_pages
It will show the source code of the function.
For triggers:
I dont't know if there is a direct way to get the source code. Just know the following way, may be it will help you!
  • step 1 : Get the table oid of the trigger:
    skytf=> select tgrelid from pg_trigger  where tgname='insert_tbl_tmp_trigger';
      tgrelid
    ---------
       26599
    (1 row)
  • step 2: Get the table name of the above oid !
    skytf=> select oid,relname  from pg_class where oid=26599;
      oid  |           relname           
    -------+-----------------------------
     26599 | tbl_tmp
    (1 row)
  • step 3: list the table information
    skytf=> \d tbl_tmp
It will show you the details of the trigger of the table . Usually a trigger uses a function. So you can get the source code of the trigger function just as the above that I pointed out
Examples
1.\df+ in psql gives you the sourcecode.
2.I suggest using \df to find the name of your function, then \x for expanded output, then \df+ name_of_function
3.\df+ outputs a lot more than the code. If all you want is the code, \sf will do the trick

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL