PostgreSQL Triggers

PostgreSQL Triggers

PostgreSQL Triggers can be defined as a user-defined function that gets executed at a particular instance of operation. In other words, we can say that as the name suggests Triggers, the PostgreSQL Triggers get triggered or activated before or after a particular action.

In order to create a trigger on a PostgreSQL table, the first step is to create a function or a procedure that will perform the desired task that we want to do before or after that event. Then in the second step, we need to bind with the newly created function with the trigger of that table on which we want to apply the trigger.

The PostgreSQL supports two types of Triggers, namely:

• Row-level Trigger
• Statement-level Trigger

The row-level trigger is associated with the rows of the table whereas the statement-level trigger is linked to a particular statement on that table. For example, if any trigger is associated with the insert statement for a table, and that insert statement is executed six times, then that statement-level trigger will be executed 6 times for each statement.

For a PostgreSQL trigger, we can perform the following commands:

• CREATE Trigger
• ALTER Trigger
• DROP Trigger
• ENABLE Trigger
• DISABLE Trigger

How to create a PostgreSQL Trigger?

For the creation of a PostgreSQL trigger first, we need to create a trigger function that will be executed each time when a particular trigger is invoked. 

The trigger functions are similar to the usual user-defined functions. The mere difference between the trigger function and the user-defined function is that the return type of trigger will always be Trigger and it will accept no parameters.

The syntax to be followed for creating a Trigger function is:

 CREATE FUNCTION name_of_trigger_function()  
    RETURNS TRIGGER  
    LANGUAGE PLPGSQL 
 AS $$ 
 BEGIN 
    -- logic for trigger goes here-- 
 END; 
 $$  

Once the trigger function is created, we can use that trigger function with our trigger to bound it to a particular event of a table using the syntax:

  CREATE TRIGGER name_of_the_trigger  
  {BEFORE | AFTER} { event_to_be_handled } 
   ON name_of_table 
   [FOR [EACH] { ROW | STATEMENT }] 
   EXECUTE PROCEDURE name_of_trigger_function  

Now let us take an example for a better understanding of PostgreSQL triggers.

As shown in the image, we have created a table named employee having three columns name, age, and salary. And we have inserted four rows in the employee table. Now let us first create a trigger function for the employee table.

As shown in the image, we have created a trigger function named salary_log_updater(). This trigger function enters data into the salary_log table which stores the logs for each Update operation on the employee table, which means the salary_log table will have all the information regarding the change of the salary of any employee present in the employee table.

As shown in the image, the salary_log table has three columns namely, current_salary, updated_salary, and change_time that will be used to store the log whenever the salary of an employee is updated in the employee table.

So now our trigger function is created successfully. Let us create the trigger for the employee table.

A statement-level trigger ‘salary_logger’ on the employee table is created successfully. So let us see how it works when we try to update the salary in the employee table.

As shown in the image, we have updated the salary of a record in the employee table and the changes are successfully reflected in the employee table that means our Update statement is executed successfully.

Let us check our salary_log table to verify that our trigger has been invoked properly and the trigger function is called successfully.

As visible in the above image, there is one record inserted in the salary_log table that means our trigger is invoked properly and the associated trigger function is also called successfully.

How to disable a PostgreSQL Trigger?

There are some scenarios when we don’t want to use a trigger on that table. So instead of deleting or dropping the trigger, we can simply disable that trigger for that table.

The syntax for disabling a PostgreSQL Trigger is:

 ALTER TABLE name_of_the_table 
 DISABLE TRIGGER name_of_the_trigger; 

How to drop a PostgreSQL Trigger?

In order to drop a PostgreSQL Trigger, use the syntax:

 DROP TRIGGER [IF EXISTS] name_of_the_trigger  
 ON name_of_the_table;  

So, with the help of this article, we got a clear idea about the PostgreSQL Triggers and their usage in PostgreSQL.