PostgreSQL – TRIGGERS: 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 Triggers. You will also gain a holistic understanding of PostgreSQL, its key features, different operations associated with PostgreSQL Triggers, their syntax, and example use cases.

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

Table of Contents

• What is PostgreSQL?
• What are PostgreSQL Triggers?
• What are all Operations Associated with PostgreSQL Triggers?
• Advantages of using PostgreSQL Triggers
• Conclusion

What is PostgreSQL?

PostgreSQL is a high-performance relational database. It is well-known for its open-source platform, which supports all RDBMS features. It has indexes, views, stored procedures, triggers, atomicity characteristics, and other features along with RDBMS capabilities. PostgreSQL also supports SQL and JSON queries. It can also be used as a data warehouse system.

PostgreSQL runs on various operating systems, including Windows, Linux, macOS, and UNIX. PostgreSQL’s source code is freely available under an open-source license, allowing anyone to use, change, and implement it as needed. Since PostgreSQL has no license fees, there is no risk of over-deployment, which reduces costs.

In this guide, before gaining in-depth information about using triggers in PostgreSQL Tables, you can have a comprehensive idea about PostgreSQL Tables.

Key Features of PostgreSQL

Some of the key features of PostgreSQL are as follows:

• Customizable: PostgreSQL can be altered and customized by writing plugins to make the DBMS meet your needs. It also lets you integrate custom functions written in other programming languages like Java, C, C++, and others.
• Community Support: A professional community is always available to its users. PostgreSQL also has a large range of private, third-party support services available.
• Open-Source: As a free and open-source solution, PostgreSQL offers Object-Oriented and Relational Database capabilities.
• Users: It is a well-known and commonly used RDBMS. Apple, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, and many other companies utilize PostgreSQL.
• Code quality: Every line of code in PostgreSQL is evaluated by a team of professionals, and the whole development process is driven by the community, allowing for speedy bug reporting, changes, and verification.
• Data Availability and Resiliency: PostgreSQL versions that are privately supported provide additional high availability, resilience, and security for mission-critical production settings such as government agencies, financial institutions, and healthcare providers.

What are PostgreSQL Triggers?

A PostgreSQL trigger is a function called automatically whenever an event such as an insert, update, or deletion occurs.

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).

What are all Operations Associated with PostgreSQL Triggers?

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 Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 150+ Data Sources (including 40+ Free Sources) such as PostgreSQL allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows the integration of data from non-native sources using Hevo’s in-built REST API & Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

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.