PostgreSQL Triggers With Examples

What are Triggers in PostgreSQL?

In PostgreSQL, Triggers are user-defined functions that automatically jump into action and perform tasks when specific events happen in your database. Whether it's adding new data (INSERT), updating already existing records (UPDATE), deleting specific records (DELETE), or wiping out all the records (TRUNCATE)!

They're for making things happen without needing a constant watch. So, if you're ready to upgrade your database's brainpower :brain:, let's travel back to the future of tech and unravel the world of PostgreSQL triggers!

Types of Triggers in PostgreSQL

There are two types of Triggers in PostgreSQL --- Row-Level Triggers and Statement-Level Triggers.

Row-Level Triggers in PostgreSQL

Row-level triggers are database objects that fire in response to specific events occurring on individual rows in a table.

We can use row-level triggers for the following use cases:

• Data Validation: We can use row-level triggers to enforce data integrity rules on a per-row basis. For example, preventing an UPDATE statement that would violate database constraints.
• Logging & Auditing: Row-level triggers are useful for capturing changes made to specific rows, including who made the change, and the time of change.
• Derived Values: If you need to calculate a derived value based on the data in a specific row, a row-level trigger can perform the calculations and update the row accordingly.

Statement-Level Triggers in PostgreSQL

Statement-level triggers are database objects that fire in response to specific events occurring on sets of rows affected by a single SQL statement.

We can use statement-level triggers for the following use cases:

• Bulk Operations: Statement level triggers come in handy when you want to perform an action on a group of rows collectively.
• Aggregations: They can be used to calculate aggregate values across multiple rows affected by a single statement.
• Logging & Auditing: If you want to capture changes made by a single SQL statement that affects multiple rows, a statement-level trigger can capture that information.

Now that you are familiar with Row-level triggers and Statement-level triggers, it is also important to know their differences.

Creating a Trigger in PostgreSQL

To create a new trigger you first need to create a trigger function using CREATE FUNCTION statement and then bind that function to a table using CREATE TRIGGER statement.

Syntax for Creating Triggers in PostgreSQL

Syntax Explanation:

• trigger_function_name(): This is the name of the trigger function you define. It contains the logic that will be executed when the trigger is fired.
• RETURNS TRIGGER: Specifies that the trigger function returns a trigger object.
• BEGIN and END: Encloses the logic to be executed by the trigger function.
• RETURN NEW: Indicates that the new/modified row is returned by the trigger function.
• CREATE [ OR REPLACE ] TRIGGER: Defines the trigger itself. The CREATE OR REPLACE keyword ensures that a new procedure will be created, irrespective of a procedure already existing with the same name. The OR REPLACE part of the statement is optional, avoided in case you don't want to replace an already existing procedure by mistake.
• [ BEFORE | AFTER ]: It specifies the timing
• [ INSERT | UPDATE | DELETE ]: and event that will activate the trigger function.
• ON table_name: Specifies the name of the table for which the trigger is created.
• FOR EACH ROW: Specifies that the trigger function is executed once for each affected row.
• EXECUTE FUNCTION trigger_function_name(): Specifies the trigger function to execute when the trigger is activated.

Example

Before proceeding to create the trigger, it's essential to first establish the definition of the table on which the trigger will operate.

Let's assume we have a table called Employees with the following structure:

Now we will create this table and populate it with some data to play with. :joystick:

The resultant table of this code will be:

Now that we have a table to work on, let's create a trigger that will fire each time we insert a new record into the table or update an existing record.

The trigger will set the employee's salary automatically based on their department.

Trigger Code

In the above code, the update_salary_based_on_department function is invoked by the salary_update_trigger trigger. It checks the emp_dept column of the newly inserted or updated row and sets the emp_salary column accordingly based on the department.

Wanna see it working? Let us insert a new record into the Employees table:

Upon the insertion of a new record into the Employees table, the trigger will fire and set Hagemaru Gupta's salary to $60000$ based on the 'IT' department.

Output:

Altering a Trigger in PostgreSQL

Changes the definition of an already existing trigger.

We can use the ALTER TRIGGER statement for 2 different purposes:

1. Renaming a trigger: Using RENAME clause with ALTER TRIGGER statement to rename a trigger without interfering with its definition.
2. Marking the dependency of a trigger on an extension: Using DEPENDS ON EXTENSION to mark the dependency of a trigger on an extension. If the trigger is dependent on the extension, the trigger will be automatically dropped when dropping the extension. If not, the trigger will stay on the database even if the extension is dropped.

Syntax for Altering Triggers in PostgreSQL

Syntax for renaming triggers

Syntax for marking dependency of a trigger on an extension

Syntax Explanation:

• ALTER TRIGGER: Modifies an existing trigger.
• trigger_name: Name of the trigger to be altered.
• ON table_name: Specifies the table for which the trigger should be modified.
• RENAME TO new_trigger_name: Renames the trigger with new_trigger_name.
• [ NO ]: If NO is absent, that means the trigger is dependent on extension and vice-versa.
• DEPENDS ON EXTENSION: Marks the trigger's dependency on the extension.
• extension_name: The name of the extension.

Example

In the previous section, we created a trigger called salary_update_trigger. Let us alter that trigger to further our practical knowledge of triggers.

We will rename that trigger to salary_per_department_trigger.

Alter Trigger Code

This code will change the name of salary_update_trigger to salary_per_department_trigger!

Disabling a Trigger in PostgreSQL

Disabling a trigger means that it will not fire when an event associated with it occurs, but it will still remain in the database.

Syntax for Disabling Triggers in PostgreSQL

Syntax Explanation:

• ALTER TABLE: To alter the definition of the table.
• table_name: Name of the table to be altered.
• DISABLE TRIGGER: To disable the trigger on the table.
• [ trigger_name | ALL | USER ]: trigger_name disables the specified trigger. ALL disables all the triggers on table_name table. USER disables all the triggers on table_name that are owned by the current user.

Example

In the previous section, we renamed our trigger to salary_per_department_trigger, now we will disable it as we don't need it for now.

Disable Trigger Code

In the above code, we are disabling the salary_per_department_trigger on the table Employees.

Let's see what will happen if we attempt to access a disabled trigger. We will add a new record into the Employees table to check if the trigger fires or not.

Resultant Employees Table:

Upon the insertion of a new record into the Employees table, the trigger will not fire and the emp_salary attribute of the new employee will be NULL.

Enabling a Trigger in PostgreSQL

In the last section, we learnt to disable triggers. Disabled triggers are not able to fire and in order to make them fire, we enable those triggers.

Syntax for Enabling Triggers in PostgreSQL

Syntax Explanation:

• ALTER TABLE: To alter the definition of the table.
• table_name: Name of the table to be altered.
• ENABLE TRIGGER: To enable the trigger on the table.
• [ trigger_name | ALL | USER ]: trigger_name enables the specified trigger. ALL enables all the triggers on table_name table. USER enables all the triggers on table_name that are owned by the current user.

Example

In the previous section, we disabled our trigger salary_per_department_trigger, now we will enable it again as we need it now.

Enable Trigger Code

In the above code, we are enabling the salary_per_department_trigger on the table Employees.

Dropping a Trigger in PostgreSQL

The DROP TRIGGER command removes an existing trigger definition.

Syntax for Dropping Triggers in PostgreSQL

Syntax Explanation:

• DROP TRIGGER: To drop the definition of the trigger.
• [ IF EXISTS ]: Specifies not to throw an error if the specified trigger does not exist.
• trigger_name: Name of the trigger to be dropped.
• ON table_name: The trigger being dropped belongs to this table.
• [ CASCADE | RESTRICT ]: CASCADE drops all the objects that depend on the trigger and in turn all the objects that depend on those objects. RESTRICT refuses to drop the trigger if any object is dependent on it, this is the default option.

Example

In the previous section, we enabled our trigger salary_per_department_trigger and now will drop it. So long, partner! :slightly_frowning_face:

Drop Trigger Code

What will happen now if we add a new record into the Employees table? Let's see.

Resultant Employees Table:

Upon the insertion of a new record into the Employees table, the trigger will not fire and the emp_salary attribute of the new employee will be NULL as the trigger that adds the salary of new employees has been dropped.

Examples of Triggers in PostgreSQL

1. Audit Logging Trigger in PostgreSQL

Audit Logging Trigger logs changes made to a table by inserting a record into an audit log table whenever an INSERT, UPDATE, or DELETE operation is performed.

At first, we will create two tables - Groceries and audit_log.

The schema of the tables will look like:

Groceries Schema

audit_log Schema

Let's populate the Groceries table with real-world data.

The resultant table of this will be:

Now we will create the trigger function.

At this point, no triggers are associated with the Groceries table yet. We should change that.

Creating audit trigger.

After this step, our trigger has been created. The trigger is now set up and ready to log changes. Hurray! :stuck_out_tongue:

Now we will perform database operations on Groceries table to see if the changes are logged into the audit_log table.

The changes will be as follows in the Groceries Table:

The audit_log table will look like:

As you can see, the Audit logs trigger is working just fine!

2. Enforcing Data Integrity Using Triggers in PostgreSQL

Triggers can be used to enforce complex data constraints or business rules that cannot be handled solely by simple constraints.

In the previous example, we created an Audit Logging Trigger on the Groceries table. In this example, we will create a Data Integrity Enforcing Trigger on the same table.

The Grocery table from the previous example is as follows:

Creating the Data Integrity Enforcing Trigger Function:

This trigger function raises an exception if the quantity of a grocery item is less than $0$.

Now we will create the Data Integrity Enforcing Trigger:

The quantity_check_trigger is created.

Now we will insert a negative quantity record inside the Groceries table to see if the trigger fires or not.

The following exception is raised:

Therefore, our Data Integrity Enforcing Trigger is also working fine. :relieved: