Summary: in this tutorial, we will show you step by step how to create the first trigger in PostgreSQL using the CREATE TRIGGER statement.
To create a new trigger in PostgreSQL, you need to:
- Create a trigger function using
CREATE FUNCTIONstatement. - Bind this trigger function to a table using
CREATE TRIGGERstatement.
If you are not familiar with creating user-defined function, you can check it out the PostgreSQL stored procedure section.
Creating the trigger function
A trigger function is similar to an ordinary function, except that it does not take any arguments and has return value type triggeras follows:
1 | CREATE FUNCTION trigger_function() RETURN trigger AS |
Notice that you can create trigger functions using any languages supported by PostgreSQL. In this tutorial, we will use PL/pgSQL for the demonstration.
The trigger function receives data about their calling environment through a special structure called TriggerData, which contains a set of local variables. For example, OLD and NEW represent the states of row in the table before or after the triggering event. PostgreSQL provides other local variables starting with TG_ as the prefix such as TG_WHEN, TG_TABLE_NAME, etc.
Once the trigger function is defined, you can bind it to specific actions on a table.
Creating the trigger
To create a new trigger, you use the CREATE TRIGGER statement. The complete syntax of the CREATE TRIGGER is complex with many options. However, for the sake of demonstration, we will use the simple form of the CREATE TRIGGER syntax as follows:
1 2 3 4 | CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {event [OR ...]} ON table_name [FOR [EACH] {ROW | STATEMENT}] EXECUTE PROCEDURE trigger_function |
The event could be INSERT, UPDATE, DELETE or TRUNCATE. You can define trigger that fires before ( BEFORE) or after ( AFTER) event. The INSTEAD OF is used only for INSERT, UPDATE, or DELETE on the views.
As mentioned in the introduction to PostgreSQL trigger, PostgreSQL provides two kinds of triggers: row level trigger and statement level trigger, which can be specified by FOR EACH ROW (row level trigger) or FOR EACH STATEMENT (statement level trigger).
PostgreSQL Trigger example
Let’s take a look at an example of creating a new trigger. In this example, we will create a new table named employees as follows:
1 2 3 4 5 | CREATE TABLE employees( id int4 serial primary key, first_name varchar(40) NOT NULL, last_name varchar(40) NOT NULL ); |
Whenever employee’s last name changes, we will log it into a separate table named employee_audits through a trigger.
1 2 3 4 5 6 | CREATE TABLE employee_audits ( id int4 serial primary key, employee_id int4 NOT NULL, last_name varchar(40) NOT NULL, changed_on timestamp(6) NOT NULL ) |
First, we define a new function called log_last_name_changes:
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE OR REPLACE FUNCTION log_last_name_changes() RETURNS trigger AS $BODY$ BEGIN IF NEW.last_name <> OLD.last_name THEN INSERT INTO employee_audits(employee_id,last_name,changed_on) VALUES(OLD.id,OLD.last_name,now()); END IF; RETURN NEW; END; $BODY$ |
The function checks if the last name of employee changes, it will insert the old last name into the employee_audits table including employee id, last name, and the time of change.
Second, we bind the trigger function to the employees table. The trigger name is last_name_changes. Before the value of the last_name column is updated, the trigger function is automatically invoked into log the changes.
1 2 3 4 5 | CREATE TRIGGER last_name_changes BEFORE UPDATE ON employees FOR EACH ROW EXECUTE PROCEDURE log_last_name_changes(); |
Third, we can insert some sample data for testing. We insert two rows into the employees table.
1 2 3 4 5 | INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe'); INSERT INTO employees (first_name, last_name) VALUES ('Lily', 'Bush'); |
We can examine the employees table:
1 | SELECT * FROM employees; |
Suppose Lily Bush gets married and she needs to change her last name to Lily Brown. We can update it as the following query:
1 2 3 | UPDATE employees SET last_name = 'Brown' WHERE ID = 2; |
We check if the last name of Lily has been updated.
1 | SELECT * FROM employees; |
As you see, the Lily’s last name has been updated. Let’s check the employee_audits table:
1 2 | SELECT * FROM employee_audits; |
As you see the change was logged into the employee_audits table by the trigger.
In this tutorial, we have shown you a simple example of creating PostgreSQL trigger to audit the changes.