How To Create Trigger In PostgreSQL
Posted By : Sanjay Saini | 25-Dec-2017
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
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Sanjay Saini
Sanjay has been working on web application development using frameworks like Java, groovy and grails. He loves listening to music , playing games and going out with friends in free time.