Introduction:
TRIGGERS in PostgreSQL are a special user-defined function that is automatically executed when a specific event occurs in a table. Unlike regular functions, which the user must explicitly call, triggers are event-driven and activate in response to INSERT, UPDATE, DELETE, or TRUNCATE operations.
PostgreSQL has supported triggers for many years, but newer enhancements, especially since PostgreSQL 10, have made them more powerful. Triggers can be defined at the row level (executing once per row) or the statement level (executing once per query). These capabilities make PostgreSQL a flexible and robust choice for data-driven applications.
Triggers are commonly used to enforce business rules, maintain data integrity, log changes, or automate complex workflows.
Types of Triggers in PostgreSQL
Triggers in PostgreSQL can be categorized based on their execution level:
1. Row-Level Triggers:
- Executed once for each row affected by the triggering event.
- Useful when individual row processing is required.
- Example: If an UPDATE query modifies 10 rows, the trigger function executes 10 times, once for each updated row.
2. Statement-Level Triggers:
- Executed once per SQL statement, regardless of how many rows are affected.
- It is ideal for auditing or enforcing constraints at a higher level than individual rows.
- Example: If an UPDATE query modifies 10 rows, the trigger function executes only once for the entire statement.
Steps to Create Triggers in PostgreSQL
To create a new trigger in PostgreSQL, you follow these steps:
First, create a trigger function using the CREATE FUNCTION statement.
Second, bind the trigger function to a table using the CREATE TRIGGER statement.
A trigger function is similar to an ordinary function. However, a trigger function does not take any argument and has a return value with the trigger type.
Creating a Function in PostgreSQL
The syntax for creating a function to be used as a trigger is as follows:
CREATE FUNCTION trigger_function()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
-- Trigger logic goes here
END;
$$;Creating a Trigger in PostgreSQL:
The syntax for creating a trigger is:
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | DELETE | UPDATE | TRUNCATE }
ON table_name
FOR { EACH ROW | EACH STATEMENT }
EXECUTE PROCEDURE trigger_function();Trigger Name: Specify the name of the trigger after the CREATE TRIGGER statement.
Trigger Timing: Define when the trigger should be firedâBEFORE or AFTER the event occurs.
Trigger Event: Specify the event that invokes the trigger. Possible events include INSERT, DELETE, UPDATE, or TRUNCATE.
Target Table: Mention the table on which the trigger is defined using the ON table_name clause.
Types of Triggers :
Row-Level Trigger: Use FOR EACH ROW to execute the trigger once per affected row.
Statement-Level Trigger: Use FOR EACH STATEMENT to execute the trigger once per statement.
Data Auditing with Triggers
Let’s create a trigger that logs changes when a player’s name is updated.
1. Creating the player Table
test=# CREATE TABLE player (
Player_id serial primary key,
Name varchar(100)
);
CREATE TABLE2. Creating the player_audit Table
test=# CREATE TABLE player_audit (Player_audit_id serial primary key,
Player_id int not null, Name varchar(100) not null, Edit_date timestamp not null);
CREATE TABLE3. Creating a Trigger Function
We first create a function and then bind that function to our trigger.
CREATE OR REPLACE FUNCTION fn_player_name_change_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
-- Compare the new value with the old value
-- 'NEW' refers to the updated row, 'OLD' refers to the existing row
IF NEW.name <> OLD.name THEN
-- Insert the old data into the audit table before updating the main table
INSERT INTO player_audit (player_id, name, edit_date)
VALUES (OLD.player_id, OLD.name, NOW());
END IF;
-- Return the new row to complete the update process
RETURN NEW;
END;
$$;
CREATE FUNCTIONThe old represents the row before the update while the new represents the new row that will be updated.
4. Binding the Function to a Trigger
Bind the newly created function to our âplayerâ table via the create trigger statement.
test=# CREATE TRIGGER trg_player_name_change
BEFORE UPDATE
ON players
FOR EACH ROW
EXECUTE PROCEDURE fn_player_name_change_log();
CREATE TRIGGERI have created a trigger function.
If you want the trigger to execute after the update instead of before, replace BEFORE with AFTER as needed.
Insert Sample Data
test=# INSERT INTO player VALUES
(1, 'Virat Kohli'),
(2, 'M.S. Dhoni'),
(3, 'Shikhar Dhawan');
INSERT 0 3View the Data
test=# select * from player;
player_id | name
-----------+----------------
1 | Virat Kohli
2 | M.S. Dhoni
3 | Shikhar Dhawan
(3 rows)Update a Player’s Name
Let’s update some data
test=# UPDATE player
SET name = 'KLrahul'
WHERE player_id = 3;
UPDATE 1View the Audit Log
test=# select * from player_audit;
player_audit_id | player_id | name | edit_date
-----------------+-----------+----------------+----------------------------
1 | 3 | Shikhar Dhawan | 2025-02-26 12:31:01.435768
(1 row)Conclusion:
Triggers in PostgreSQL provide a powerful mechanism for automating database operations, maintaining data integrity, and enforcing business rules. By understanding the difference between row-level and statement-level triggers, developers can optimize database performance and ensure consistency in data changes.
In this guide, we demonstrated how to create a trigger function, bind it to a table, and use it to audit changes efficiently. Implementing triggers correctly can help in logging modifications, preventing unwanted changes, and streamlining database workflows.
In the next section (Part 2), we will focus on Statement-Level Triggers, exploring their use cases, implementation, and best practices for handling large-scale database operations.