• First, create a trigger function usingCREATE FUNCTION statement.
• Second, bind the trigger function to a table by usingCREATE TRIGGER statement.

If you are not familiar with creating a user-defined function, you can check out thePL/pgSQL section.

Create trigger function syntax

A trigger function is similar to a regularuser-defined function. However, a trigger function does not take any arguments and has a return value with the typetrigger.

The following illustrates the syntax of creating a trigger function:

CREATE FUNCTION trigger_function()   RETURNS TRIGGER   LANGUAGE PLPGSQLAS $$BEGIN   -- trigger logicEND;$$

Notice that you can create a trigger function using any language supported by PostgreSQL. In this tutorial, we will use PL/pgSQL.

A trigger function receives data about its calling environment through a special structure calledTriggerData which contains a set of local variables.

For example,OLD andNEW represent the states of the row in the table before or after the triggering event.

PostgreSQL also provides other local variables preceded byTG_ such asTG_WHEN, andTG_TABLE_NAME.

After creating a trigger function, you can bind it to one or more trigger events such asINSERT,UPDATE, andDELETE.

Introduction to PostgreSQL CREATE TRIGGER statement

TheCREATE TRIGGER statement allows you to create a new trigger.

The following illustrates the basic syntax of theCREATE TRIGGER statement:

CREATE TRIGGER trigger_name   {BEFORE |AFTER} {event }   ON table_name   [FOR [EACH] {ROW |STATEMENT }]       EXECUTE PROCEDURE trigger_function

In this syntax:

First, provide the name of the trigger after theTRIGGER keywords.

Second, indicate the timing that causes the trigger to fire. It can beBEFORE orAFTER an event occurs.

Third, specify the event that invokes the trigger. The event can beINSERT ,DELETE,UPDATE orTRUNCATE.

Fourth, specify the name of the table associated with the trigger after theON keyword.

Fifth, define the type of triggers, which can be:

• The row-level trigger that is specified by theFOR EACH ROW clause.
• The statement-level trigger that is specified by theFOR EACH STATEMENT clause.

A row-level trigger is fired for each row while a statement-level trigger is fired for each transaction.

Suppose a table has 100 rows and two triggers that will be fired when aDELETE event occurs.

If theDELETE statement deletes 100 rows, the row-level trigger will fire 100 times, once for each deleted row. On the other hand, a statement-level trigger will be fired for one time regardless of how many rows are deleted.

Finally, give the name of the trigger function after theEXECUTE PROCEDURE keywords.

PostgreSQL CREATE TRIGGER example

The following statement creates a new table calledemployees:

CREATE TABLE employees(   idINT GENERATED ALWAYS AS IDENTITY,   first_nameVARCHAR(40)NOT NULL,   last_nameVARCHAR(40)NOT NULL,   PRIMARY KEY(id));

Note that if your database already has theemployees table, you can drop it first before creating a new one:

DROP TABLE IF EXISTS employees;

Suppose that when the name of an employee changes, you want to log it in a separate table calledemployee_audits :

CREATE TABLE employee_audits (   id INT GENERATED ALWAYS AS IDENTITY,   employee_id INT NOT NULL,   last_name VARCHAR(40)NOT NULL,   changed_on TIMESTAMP NOT NULL);

First, create a new function calledlog_last_name_changes:

CREATE OR REPLACE FUNCTION log_last_name_changes()  RETURNS TRIGGER  LANGUAGE PLPGSQL  AS$$BEGINIF NEW.last_name<> OLD.last_nameTHEN INSERT INTO employee_audits(employee_id,last_name,changed_on) VALUES(OLD.id,OLD.last_name,now());END IF;RETURN NEW;END;$$

The function inserts the old last name into theemployee_audits table including employee id, last name, and the time of change if the last name of an employee changes.

TheOLD represents the row before the update while theNEW represents the new row that will be updated. TheOLD.last_name returns the last name before the update and theNEW.last_name returns the new last name.

Second, bind the trigger function to theemployees table. The trigger name islast_name_changes. Before the value of thelast_name column is updated, the trigger function is automatically invoked to log the changes.

CREATE TRIGGER last_name_changes  BEFORE UPDATE  ON employees  FOR EACHROW  EXECUTE PROCEDURE log_last_name_changes();

Third,insert some rows into theemployees table:

INSERT INTO employees (first_name, last_name)VALUES ('John','Doe');INSERT INTO employees (first_name, last_name)VALUES ('Lily','Bush');

Fourth, examine the contents of theemployees table:

SELECT * FROM employees;

Suppose thatLily Bush changes her last name toLily Brown.

Fifth, update Lily’s last name to the new one:

UPDATE employeesSET last_name= 'Brown'WHERE ID= 2;

Sixth, check if the last name ofLily has been updated:

SELECT * FROM employees;

The output indicates that Lily’s last name has been updated.

Finally, verify the contents of theemployee_audits table:

SELECT * FROM employee_audits;

The change was logged in theemployee_audits table by the trigger.

Summary

• Use the PostgreSQLCREATE TRIGGER to create a new trigger.

Last updated onMarch 30, 2024

On this page

• Create trigger function syntax
• Introduction to PostgreSQL CREATE TRIGGER statement
• PostgreSQL CREATE TRIGGER example
• Summary