How to create a trigger in postgresql or plpgsql

How to create a trigger in postgresql or plpgsql?

    1. Create two tables as shown below. The dbo.emp table will store the sample user data where as dbo.emp_audit will store the data which is triggered from the trigger function.

CREATE TABLE dbo.emp (ID INT, NAME VARCHAR(10), SALARY INTEGER);

CREATE TABLE dbo.emp_audit (ID INT, NAME VARCHAR(10), SALARY INTEGER, CHANGED_DATE TIMESTAMP WITHOUT TIME ZONE, DESCRIPTION TEXT);

    2. Create a trigger function to run the actual PLPGSQL statements when a trigger fires. That means when an INSERT, DELETE or UPDATE operation occurred on dbo.emp table, then the corresponding trigger will execute the following function dbo.tfn_emp_insert.

CREATE OR REPLACE FUNCTION dbo.tfn_emp_insert()

RETURNS TRIGGER AS

$BODY$

BEGIN

INSERT INTO dbo.emp_audit SELECT NEW.ID, NEW.NAME, NEW.SALARY, NOW()::TIMESTAMP, ‘INSERT’;

RETURN NEW;

END

$BODY$

LANGUAGE PLPGSQL;

    3. Create a trigger on the table emp as shown below. The following trigger will execute the function tfn_emp_insert when an insert operation occurs in the dbo.emp table.

CREATE TRIGGER emp_inst

AFTER INSERT

ON dbo.emp

FOR EACH ROW

EXECUTE PROCEDURE dbo.fn_emp_insert();

    4. Let’s test the trigger functionality by inserting few records in dbo.emp table.

INSERT INTO dbo.emp SELECT 1,’one1’,1000;

INSERT INTO dbo.emp SELECT 2,’two2’,2000;

INSERT INTO dbo.emp SELECT 3,’three3’,3000;

    5. Verify the both table’s data to see whether the trigger is working as expected.

SELECT * FROM dbo.emp;

SELECT * FROM dbo.emp_audit;

Notes: If you want to catch the each and every row by using OLD and NEW variables, then use the “FOR EACH ROW” statement in the trigger script.