PostgreSQL – CREATE TRIGGER
To create a new trigger in PostgreSQL, you follow these steps:
- First, create a trigger function using CREATE FUNCTION statement.
- Second, bind the trigger function to a table by using the CREATE TRIGGER statement.
A trigger function is similar to an ordinary function. However, a trigger function does not take any argument and has a return value with the type of trigger.
The following illustrates the syntax of creating a trigger function:
Syntax: CREATE FUNCTION trigger_function() RETURNS trigger AS
A trigger function receives data about its calling environment through a special structure called TriggerData, which contains a set of local variables. Once you define a trigger function, you can bind it to one or more triggers events such as INSERT, UPDATE, and DELETE.
Let’s take a look at an example of creating a new trigger. In this example, we will create a new table named COMPANY as follows:
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
When the name of an employee changes, we log the changes in a separate table named AUDIT :
CREATE TABLE AUDIT( EMP_ID INT NOT NULL, ENTRY_DATE TEXT NOT NULL );
First, define a new function called auditlog():
CREATE OR REPLACE FUNCTION auditlog() 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;
The function inserts the old last name into the AUDIT table including employee id, last name, and the time of change if the last name of an employee changes. Second, bind the trigger function to the employees table. The trigger name is name changes. Before the value of the name column is updated, the trigger function is automatically invoked to log the changes.
CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlog();
Third, insert some sample data for testing. We insert two rows into the employees table.
INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, 'Raju', 25, 'New-Delhi', 33000.00 );
To examine the employees table use the below query:
SELECT * FROM COMPANY;