PostgreSQL Trigger and its Types

What are the triggers: The PostgreSQL contains database callback functions that are automatically applied when a specified action occurs within the database. In PostgreSQL, triggers are associated with a particular table or view and execute the specified function on the occurrence of specific events.

Why we Need Trigger:

1- Triggers help the database designer ensure specific actions, such as maintaining an audit file, are completed regardless of which program or user makes changes to the data.
2- To improve data integrity, the trigger can be used. When an action is performed on data, it is possible to check if the manipulation of the data concurs with the underlying business rules, and thus avoids erroneous entries in a table.

There are three types of triggers available in PostgreSQL

1- After Trigger
2- Before Trigger
3- instead of Trigger

When
Event
Row-level
Statement-level
BEFORE
INSERT/UPDATE/DELETE
Tables
Tables and views
AFTER
INSERT/UPDATE/DELETE
Tables
Tables and views
INSTEAD OF
INSERT/UPDATE/DELETE
Views
TRUNCATE
Tables


For example creating table first:

CREATE TABLE public.employee
(
    empid serial NOT NULL,
    fname text,
    lname text,
    username text,
    password text,
    contact text,
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL
);

After Trigger:

This trigger occurs after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed).

Syntax:

CREATE TRIGGER trigger_name
          AFTER <action/ insert/delete/update> ON Table_Name
          FOR EACH ROW
          EXCECUTE PROCEDURE function_name

         
Callback function: This function will execute automatically through trigger "employee_update" once an event occurs on the "Employee."

CREATE OR REPLACE FUNCTION check_employee_update() RETURNS TRIGGER AS $employee$ 
    BEGIN 
        INSERT INTO audit
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
                                                password,
                                                contact,
                                                salary,
                                                doj,
                                                created_date
                                )
                                VALUES
                                (
                                                new.empid,
                                                new.fname,
                                                new.lname,
                                                new.username,
                                                new.password,
                                                new.contact,
                                                new.salary,
                                                new.doj,
                                                current_timestamp
                                ); 
        RETURN NEW;  
    END; 
$employee$ LANGUAGE plpgsql;  



Example for UPDATE (AFTER TRIGGER).

Any changes in a row will trigger the following example.

CREATE TRIGGER after_employee_update
    AFTER UPDATE ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();

         
1- Insert records in employee table- No Action
2- Update records in employee table- Trigger fire
3- Delete records from employee table- No action

If you want to execute check_employee_update function if salary value will be changed, see the following trigger example.


CREATE TRIGGER after_employee_update_for_salary
    AFTER UPDATE  Of Salary ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();


1- Insert records in employee table- No Action
2- Update records in employee table- The trigger will fire but only when salary will update.
3- Delete records from employee table- No action

Example for INSERT (AFTER TRIGGER):


CREATE TRIGGER after_employee_insert
    AFTER INSERT ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();


1- Insert records in employee table- Trigger fire
2- Update records in employee table- No Action
3- Delete records from employee table- No Action

Example for DELETE (AFTER TRIGGER):

If you are using the trigger on delete you have to make a little bit change in the above function (check_employee_update) because we are considering new value in check_employee_update while it will be not finding any new records, it will work for the INSERT and UPDATE but failed for DELETE, I am creating a new function as below for the Delete trigger.


CREATE OR REPLACE FUNCTION check_employee_delete() RETURNS TRIGGER AS $employee$ 
    BEGIN 
        INSERT INTO audit
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
                                                password,
                                                contact,
                                                salary,
                                                doj,
                                                created_date
                                )
                                VALUES
                                (
                                                old.empid,
                                                old.fname,
                                                old.lname,
                                                old.username,
                                                old.password,
                                                old.contact,
                                                old.salary,
                                                old.doj,
                                                current_timestamp
                                ); 
        RETURN OLD;  
    END; 
$employee$ LANGUAGE plpgsql;


Note: I am considering old value after delete. If you are using the same audit table for all operations, then you can use a flag column in the audit table to recognize the action.

Trigger:

CREATE TRIGGER after_employee_delete
    AFTER DELETE ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_delete();


1- Insert records in employee table- No Action
2- Update records in employee table- No Action
3- Delete records from employee table- Trigger fire

Before Trigger

This trigger occurs before the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has ended).

Syntax:

CREATE TRIGGER trigger_name
          BEFORE <action/ insert/delete/update> ON Table_Name
          FOR EACH ROW
          EXCECUTE PROCEDURE function_name

         
Note: BEFORE TRIGGER is used when you want to perform any task before any changes affect the table.

Example for UPDATE (BEFORE TRIGGER)

I am using the same function above described

CREATE OR REPLACE FUNCTION check_employee_update() RETURNS TRIGGER AS $employee$ 
    BEGIN 
        INSERT INTO audit
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
                                                password,
                                                contact,
                                                salary,
                                                doj,
                                                created_date
                                )
                                VALUES
                                (
                                                new.empid,
                                                new.fname,
                                                new.lname,
                                                new.username,
                                                new.password,
                                                new.contact,
                                                new.salary,
                                                new.doj,
                                                current_timestamp
                                ); 
        RETURN NEW;  
    END; 
$employee$ LANGUAGE plpgsql; 


Before Trigger:

CREATE TRIGGER before_employee_update
    BEFORE UPDATE ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();


1- Insert records in employee table- No Action
2- Update records in employee table- Trigger fire
3- Delete records from employee table- No action

If you want to execute check_employee_update function if salary value will be changed, see the following trigger example.

CREATE TRIGGER before_employee_update_for_salary
    BEFORE UPDATE  Of Salary ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();


1- Insert records in employee table- No Action
2- Update records in employee table- The trigger will fire but only when salary will update.
3- Delete records from employee table- No action         

Example for INSERT (BEFORE TRIGGER):


CREATE TRIGGER before_employee_insert
    BEFORE INSERT ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_update();

1- Insert records in employee table- Trigger fire
2- Update records in employee table- No Action
3- Delete records from employee table- No Action

Example for DELETE (BEFORE TRIGGER)

For the BEFORE DELETE TRIGGER for the employee table I will follow the same as above (After Delete in AFTER TRIGGER) like create a new function for delete that will insert the old records in audit.

CREATE TRIGGER before_employee_delete
    BEFORE DELETE ON employee
    FOR EACH ROW
    EXECUTE PROCEDURE check_employee_delete();
         
1- Insert records in employee table- No Action
2- Update records in employee table- No Action
3- Delete records from employee table- Trigger fire

INSTEAD of Trigger

INSTEAD of Trigger operation (in the case of inserts, updates, or deletes on a view).
Suppose we have a view in PostgreSQL and you want to perform a trigger event then you have to choose the INSTEAD of TRIGGER. I am creating a VIEW, please refer to the following PostgreSQL statement to create VIEW.


CREATE VIEW employee_view AS
SELECT * FROM employee


I have only one table (employee) so create a VIEW using it but the purpose of VIEW and its implementation I will describe in the next article.
If I am going to use INSTEAD of TRIGGER, then I have to make some changes in function.

CREATE OR REPLACE FUNCTION check_employee_view_update() RETURNS TRIGGER AS $employee$ 
    BEGIN                                
        INSERT INTO employee
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
CREATE OR REPLACE FUNCTION check_employee_view_update() RETURNS TRIGGER AS $employee$ 
    BEGIN                                
        INSERT INTO employee
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
                                                password,
                                                contact,
                                                salary,
                                                doj
                                )
                                SELECT
                                                NEW.empid,
                                                new.fname,
                                                new.lname,
                                                new.username,
                                                new.password,
                                                new.contact,
                                                new.salary,
                                                new.doj;
                                                               
                                INSERT INTO audit
                                (
                                                empid,
                                                fname,
                                                lname,
                                                username,
                                                password,
                                                contact,
                                                salary,
                                                doj,
                                                created_date
                                )
                                VALUES
                                (
                                                NEW.empid,
                                                new.fname,
                                                new.lname,
                                                new.username,
                                                new.password,
                                                new.contact,
                                                new.salary,
                                                new.doj,
                                                current_timestamp
                                );
      
                                RETURN NEW;  
    END; 
$employee$ LANGUAGE plpgsql;



In the above function, you can see I am inserting data into table employee and table audit because we know VIEW is a statement and its fetch the value from the tables at run time when we INSERT the row into VIEW we have to write some query as above function to insert rows at its related table.

INSERT SOME Records into VIEW as below,

INSERT INTO employee_view
(
          empid,
          fname,
          lname,
          username,
          password,
          contact,
          salary,
          doj
)
VALUES
(
          101,
          'Naina',
          'Singh',
          'naina@gmail.com',
          'xyz',
          '9088877767',
          120000,
          current_timestamp
);


You will see the inserted records as you wish.
Same as it is if you want to perform trigger on another action like UPDATE/DELETE, you have to change the function according to your requirements.

Please comment your suggestion/question/understanding in comment section.
You have any query you can directly contact me on dilip.ravi084@gmail.com, At the weekend definitely, I will reply.

No comments:

Post a Comment

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...