PostgreSQL TRIGGER: A Comprehensive 101 Guide

If you are using PostgreSQL to meet your database needs, you’ve probably seen some process disruptions. Manually connecting each event and action may be extremely time-consuming. This is where PostgreSQL Triggers come into play.

These are user-defined functions that can assist you in connecting and automating your plan of action based on the dependability of events. In this article, you will gain information about PostgreSQL Trigger. You will also gain a holistic understanding of different operations and PostgreSQL Trigger example, their syntax, and use cases.

Read along to find out in-depth information about creating PostgreSQL Triggers.

What are all Operations Associated with PostgreSQL Triggers?

A PostgreSQL trigger can be defined to fire in the following cases:

• Before attempting any operation on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted).
• When an operation has been completed (after constraints are checked and the INSERT, UPDATE, or DELETE has been completed).
• In spite of the operation (in the case of INSERT, UPDATE, or DELETE on a view).

The different operations that enable the working of PostgreSQL Triggers are as follows:

• Create PostgreSQL Triggers
• Drop PostgreSQL Triggers
• Alter PostgreSQL Triggers
• Disable PostgreSQL Triggers
• Enable PostgreSQL Triggers

1) Create PostgreSQL Triggers

For creating a new PostgreSQL Trigger, you need to follow these steps:

• First, you need to create a trigger function using CREATE FUNCTION statement.
• Then you need to bind the trigger function to a table by using CREATE TRIGGER statement.

1) Creating Trigger Function

You first need to create a Trigger function, a user-defined function that doesn’t take any arguments. The function returns a value of the type “trigger.”

Syntax of the Trigger Function:

CREATE FUNCTION trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- trigger logic
END;
$$

It has to be noted that you can create a Trigger Function using any of the languages supported by PostgreSQL. In this case, PL/pgSQL is used.

2) Creating a new Trigger using Create Trigger Statement

The Create Trigger statement is used for creating any PostgreSQL Trigger. 

The detailed syntax, according to PostgreSQL Documentation is as follows:

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

In the above syntax, an event can be one of:

•    INSERT
•    UPDATE [ OF column_name [, … ] ]
•    DELETE
•    TRUNCATE

However, a more simplified syntax of the CREATE TRIGGER statement would be:

CREATE TRIGGER trigger_name 
   {BEFORE | AFTER |INSTEAD OF} { event_name }
   ON table_name
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE trigger_function

In the above syntax:

• trigger_name is the name of the trigger. You need to specify the name of the trigger after the TRIGGER keyword.
• {BEFORE | AFTER | INSTEAD OF} specifies the timing when the trigger is to be fired. 
• event_name specifies the name of the event that invokes the trigger. The event can be INSERT, DELETE, UPDATE or TRUNCATE.
• table_name is the name of the table that is associated with the trigger. You need to specify the name of the table after the ON keyword.
• Then you need to specify the type of triggers that can be:
  • The FOR EACH ROW clause specifies a row-level trigger.
  • The FOR EACH STATEMENT clause specifies a statement-level trigger.

For example, the below statement showcases the creation of a trigger function and a PostgreSQL trigger.

1) Creating a Trigger Function

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
	IF NEW.last_name <> OLD.last_name THEN
		 INSERT INTO employee_audits(employee_id,last_name,changed_on)
		 VALUES(OLD.id,OLD.last_name,now());
	END IF;

	RETURN NEW;
END;
$$

2) Creating a PostgreSQL trigger

CREATE TRIGGER last_name_changes
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_name_changes();

2) Drop PostgreSQL Triggers

For removing PostgreSQL triggers, you can use the DROP TRIGGER statement. The DROP TRIGGER command helps to remove an existing trigger definition. The current user must be the owner of the table for which the PostgreSQL trigger is specified in order to run this command.

The detailed syntax according to PostgreSQL Documentation is as follows:

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

In the above syntax:

• IF EXISTS specifies not to throw an error if the trigger does not exist. A notice is issued in this case.
• name is the name of the trigger you want to remove. You need to specify the name of the trigger after the TRIGGER keyword.
• table_name is the name of the table for which the trigger is defined. The name of the table has to be mentioned after the ON keyword.
• If you want to automatically drop items that are dependent on the trigger, you can use the CASCADE option. CASCADE will also delete objects that are dependent on the trigger that is being removed. You can use the RESTRICT option to prevent the trigger from being dropped if any objects rely on it. The DROP TRIGGER command by default uses RESTRICT if not specified anything.

For example,

DROP TRIGGER username_check
ON staff;

3) Alter PostgreSQL Triggers

For changing the definition of PostgreSQL triggers, you can use the ALTER TRIGGER statement. You can use it for 2 purposes:

• Renaming a PostgreSQL trigger without changing its definition.
• For marking a PostgreSQL trigger as being dependent on an extension. 

In this case, you must be the owner of the table where the trigger to be modified is defined.

The detailed syntax, according to PostgreSQL Documentation is as follows:

1) Renaming a Trigger

ALTER TRIGGER name ON table_name RENAME TO new_name

2) Marking a PostgreSQL trigger as being dependent on an extension

ALTER TRIGGER name ON table_name [ NO ] DEPENDS ON EXTENSION extension_name

In the above syntax:

• name is the name of the trigger you want to alter. You need to specify the name of the trigger after the ALTER TRIGGER keyword.
• table_name is the name of the table for which the trigger is defined. The name of the table has to be mentioned after the ON keyword.
• new_name is the new name that you want to give the trigger. You need to specify the name of the trigger after the RENAME TO keyword.
• extension_name is the name of the extension on which you want the trigger to depend.

For example,

1) Renaming an existing Trigger:

ALTER TRIGGER emp_stamp ON emp RENAME TO emp_track_chgs;

2) Marking a Trigger as being dependent on an Extension:

ALTER TRIGGER emp_stamp ON emp DEPENDS ON EXTENSION emplib;

4) Disable PostgreSQL Triggers

For disabling PostgreSQL triggers, you can use the ALTER TABLE DISABLE TRIGGER statement. Even after disabling a PostgreSQL trigger, it still remains in the database. However, when an event associated with the trigger occurs, the disabled PostgreSQL trigger will not fire.

The syntax according to postgresqltutorials.com is as follows:

ALTER TABLE table_name
DISABLE TRIGGER trigger_name | ALL

In the above syntax:

• table_name is the name of the table with which the trigger is associated. The name of the table has to be mentioned after the ALTER TABLE keyword.
• trigger_name is the name of the trigger you want to disable. You need to specify the name of the trigger after the DISABLE TRIGGER keyword. You can also use the ALL keyword to disable all the triggers associated with the table.

For example,

1) Disabling a single PostgreSQL Trigger:

ALTER TABLE employees
DISABLE TRIGGER log_last_name_changes;

2) Disabling all PostgreSQL Triggers of a table:

ALTER TABLE employees
DISABLE TRIGGER ALL;

5) Enable PostgreSQL Triggers

For enabling one or more PostgreSQL triggers, you can use the ALTER TABLE ENABLE TRIGGER statement. When an event associated with the trigger occurs, the disabled PostgreSQL trigger will not fire. So, in order for it to fire, you need to enable the PostgreSQL Trigger.

The syntax according to postgresqltutorials.com is as follows:

ALTER TABLE table_name
ENABLE TRIGGER trigger_name |  ALL;

In the above syntax:

• table_name is the name of the table with which the trigger is associated. The name of the table has to be mentioned after the ALTER TABLE keyword.
• trigger_name is the name of the trigger you want to enable. You need to specify the name of the trigger after the ENABLE TRIGGER keyword. You can also use the ALL keyword to enable all the triggers associated with the table.

For example,

1) Enabling a single PostgreSQL Trigger:

ALTER TABLE employees
ENABLE TRIGGER salary_before_update;

2) Enabling all PostgreSQL Triggers of a table:

ALTER TABLE employees
ENABLE TRIGGER ALL;

Advantages of Using PostgreSQL Triggers

Some of the advantages of using PostgreSQL triggers are as follows:

• PostgreSQL Triggers provide another way to check the integrity of data.
• They counteract any invalid exchanges.
• They can handle any errors associated with any database.
• They can also be useful for inspecting any data changes happening in the table.
• PostgreSQL Triggers also help in forcing security approvals in any table that is present in a PostgreSQL database.

Conclusion

In this article, you learned about PostgreSQL Triggers. This article also focused on PostgreSQL, its key features, different operations associated with PostgreSQL Triggers, their syntax and example use cases. You would have also learned about the advantages of using PostgreSQL Triggers.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Visit our Website to Explore Hevo

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the transparent pricing, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding PostgreSQL Triggers in the comment section below! We would love to hear your thoughts.