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:
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,
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$
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
$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;
Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape, GeeksforGeeks Courses
are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out - check it out now!