YouTip

PostgreSQL triggers are callback functions of the database that are automatically executed/called when a specified database event occurs. Here are some important points about PostgreSQL triggers: * PostgreSQL triggers can be fired in the following situations: * Before the operation is executed (before checking constraints and attempting to insert, update, or delete). * After the operation is executed (after checking constraints and after insert, update, or delete is complete). * UPDATE operations (when inserting, updating, or deleting on a view). * The FOR EACH ROW attribute is optional for triggers. If selected, it is called once for each row when the operation modifies rows; on the contrary, if FOR EACH STATEMENT is selected, the trigger executes once per statement regardless of how many rows are modified. * The WHEN clause and trigger actions can access each row element when referencing NEW.column-name and OLD.column-name forms for INSERT, DELETE, or UPDATE. Here, column-name is the name of the column in the table associated with the trigger. * If a WHEN clause exists, PostgreSQL statements will only execute the row for which the WHEN clause is true. If there is no WHEN clause, PostgreSQL statements will execute for every row. * The BEFORE or AFTER keywords determine when the trigger action is executed, determining whether the trigger action is executed before or after the insertion, modification, or deletion of the associated row. * The table to be modified must exist in the same database as the table or view to which the trigger is attached, and must only use tablename, not database.tablename. * Constraint options are specified when creating constraint triggers. This is the same as regular triggers, except that this constraint can be used to adjust when the trigger fires. When the constraint implemented by the constraint trigger is violated, it will throw an exception. ### Syntax The basic syntax for creating a trigger is as follows: CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ -- trigger logic....]; Here, event_name can be INSERT, DELETE, and UPDATE database operations on the mentioned table table_name. You can optionally specify FOR EACH ROW after the table name. The following is the syntax for creating a trigger on one or more specified columns of a table on an UPDATE operation: CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name [ -- trigger logic....]; ### Example Let us assume a situation where we want to keep an audit trail for every record that is inserted into a newly created COMPANY table (drop and recreate if it already exists): tutorialdb=# CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); To keep the audit trail, we will create a new table named AUDIT. Whenever there is a new record entry in the COMPANY table, a log message will be inserted into it: tutorialdb=# CREATE TABLE AUDIT( EMP_ID INT NOT NULL, ENTRY_DATE TEXT NOT NULL ); Here, ID is the ID of the AUDIT record, EMP_ID is the ID from the COMPANY table, and DATE will hold the timestamp when the record was created in the COMPANY. So, now let us create a trigger on the COMPANY table as follows: tutorialdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc(); auditlogfunc() is a PostgreSQL procedure, which is defined as follows: 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; Now, let us start inserting data into the COMPANY table: tutorialdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); At this time, a record is inserted into the COMPANY table: At the same time, a record is also inserted into the AUDIT table, because we created a trigger when inserting into the COMPANY table. Similarly, we can also create triggers on UPDATE and DELETE as needed: emp_id | entry_date --------+------------------------------- 1 | 2013-05-05 15:49:59.968+05:30(1 row) ### Listing Triggers You can list all triggers in the current database from the pg_trigger table: tutorialdb=# SELECT * FROM pg_trigger; If you want to list triggers for a specific table, the syntax is as follows: tutorialdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company'; The result is as follows: tgname ----------------- example_trigger (1 row) ### Dropping Triggers The basic syntax for dropping a trigger is as follows: drop trigger ${trigger_name} on ${table_of_trigger_dependent}; The command to drop the trigger example_trigger on the company table in this article is: drop trigger example_trigger on company;