How To Create Trigger In PostgreSQL

Hi Guys,

In this blog, I will be going to explain to you with example how do we create a trigger in PostgreSQL.

Let's start with the definition of the trigger.

Trigger : 

A trigger is a PL/SQL block structure which is executed when a Data Manipulation statements like Insert,Delete, Update is executed on a database table. A trigger is triggered automatically when an associated Data Manipulation statement is executed.

Trigger Fired on below events -

1.Data Manipulation Language Statement -

•    DELETE  - delete a row.
•    INSERT   - add a new row.
•    UPDATE - update a row.

2.Data Definition Language Statement -

• CREATE - databases or objects of databases.
• ALTER - existing structure of database.
• DROP - delete tables,indexes Or database.

SYNTAX OF TRIGGER : 

CREATE [OR REPLACE ] TRIGGER trigger_name   
{BEFORE | AFTER | INSTEAD OF }   
{INSERT [OR] | UPDATE [OR] | DELETE}   
[OF col_name]   
ON table_name   
[REFERENCING OLD AS o NEW AS n]   
[FOR EACH ROW]   
WHEN (condition)    
DECLARE  
   Declaration-statements  
BEGIN   
   Executable-statements  
EXCEPTION  
   Exception-handling-statements  
END;  

Where,

CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger 
with the trigger name.

{BEFORE | AFTER | INSTEAD OF} − It uses for specifies when the trigger will be executed.

{INSERT [OR] | UPDATE [OR] | DELETE} − It specifies the Data Manipulation operation.

[OF col_name] − It specifies the name of the column that will be updated.

[ON table_name] − It specifies the name of the table connected with the trigger.

[REFERENCING OLD AS o NEW AS n] − It allows you to refer updated new and old values for various Data Manipulation statements.

[FOR EACH ROW] − It specifies a row-level trigger, i.e., the trigger will 
be executed for each row being affected.

WHEN (condition) − It uses for provides a condition for rows .

Before start with the real example, we'll create a table first.

CREATE TABLE employee (
 id serial NOT NULL PRIMARY KEY,
 name VARCHAR,
 position VARCHAR,
 city VARCHAR,
 state VARCHAR,
 salary BIGINT    
);

INSERT INTO employee (name,position,city,state,salary) VALUES('SANJAY','SOFTWARE ENGINEER','GURGAON','HARYANA',20000);
INSERT INTO employee (name,position,city,state,salary) VALUES('KUNAL','SENIOR QA','GURGAON','HARYANA',30000);
INSERT INTO employee (name,position,city,state,salary) VALUES('SUMIT','SENIOR SOFTWARE ENGINEER','GURGAON','HARYANA',50000);
INSERT INTO employee (name,position,city,state,salary) VALUES('ANJALI','QA','GURGAON','HARYANA',30000);
INSERT INTO employee (name,position,city,state,salary) VALUES('ANIL','SOFTWARE ENGINEER','GURGAON','HARYANA',60000);

SELECT * FROM employee;

OUTPUT : 

Create Trigger:

Let's take a program to create a row level trigger for the employee table that would execute for INSERT or UPDATE or DELETE operations performed on the employee table. 
This trigger will display the old  Position and updated Position values:

CREATE OR REPLACE FUNCTION position_Update() 
  RETURNS trigger AS
$$
BEGIN
   RAISE NOTICE 'Position Before Update %  ',E':\t'|| OLD.position;  
   RAISE NOTICE 'Position After  Update %  ',E':\t' || NEW.position; 
  RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';


CREATE TRIGGER position_update
BEFORE DELETE OR INSERT OR UPDATE ON employee  
FOR EACH ROW EXECUTE PROCEDURE position_Update();

When you will update Insert Or DELETE any Record in the Employee Table then trigger will be automatically executed.

For Example :  Update Position of employee Sumit .

UPDATE employee set position='LEAD' where id =3;

OUTPUT : 

Thanks 

Sanjay Saini