Module: Triggers
Learning Objectives: By the end of this lesson, students will be able to explain the purpose and types of PostgreSQL
triggers, distinguish between BEFORE, AFTER, and INSTEAD OF triggers, and identify when to use row-level versus
statement-level triggers. They will be able to create and attach trigger functions to tables, implement audit logs using
triggers, and enforce data rules such as preventing DELETE or TRUNCATE operations. Students will also learn how to
create conditional triggers that respond only to specific column changes, enabling more precise control over database
behavior.
1. Guide to PostgreSQL Triggers
1.1 Types of Triggers (Event-based)
In PostgreSQL, a trigger is a user-defined function that INSTEAD OF trigger takes over and inserts the data into
is executed automatically when a specific event occurs on the correct base tables.
a table, view, or foreign table.
There are two key points to remember: In PostgreSQL, if multiple triggers are defined on the
• A trigger is just a function, but one that runs same table, they are executed in alphabetical order
automatically. based on their names. This makes it important to pay
• It is triggered by events like INSERT, UPDATE, attention to the naming conventions you use when
DELETE, or TRUNCATE on a table, view, or creating triggers.
foreign table.
Trigger Execution Order:
Triggers can be set to run at three different times 1. All BEFORE triggers are executed first (in
relative to an event: alphabetical order).
• These run before the actual row operation
BEFORE Trigger (like INSERT, UPDATE, DELETE, or
A BEFORE trigger runs before an event happens, such TRUNCATE).
as an INSERT, UPDATE, DELETE, or TRUNCATE. • They can modify or even cancel the
This trigger can: upcoming operation.
• Stop the operation from happening (e.g., cancel 2. The actual row operation is performed next.
the insert or update). • For example, the actual insert or update
• Change the data before it is saved (e.g., modify takes place after all BEFORE triggers have
the value being inserted or updated). finished.
3. All AFTER triggers run afterward (also in
Example: Automatically correct or adjust values before alphabetical order).
they are saved to the database. • These triggers work with data that has
already been committed to the table.
AFTER Trigger • They are useful for validation, logging, or
An AFTER trigger runs after the event has already follow-up actions.
happened and the changes have been applied to the
table. It’s useful when you want to perform additional This process ensures a deterministic execution
actions once the data is already saved—like: order—you can reliably predict the sequence in which
• Logging the operation triggers will fire. Also, there’s no limit to the number of
• Updating another table triggers you can define on a table.
• Sending notifications
Trigger Capabilities:
All the final data (after changes) is available to the AFTER Triggers can modify data both before and after the main
trigger. operation.
INSTEAD OF Trigger For example:
An INSTEAD OF trigger is used mostly on views, since A BEFORE INSERT trigger might change the value
views don’t store actual data. This trigger lets you define being inserted.
a custom action to take instead of the usual INSERT, An AFTER INSERT trigger can double-check or log that
UPDATE, or DELETE. value after it's been stored.
Example: You create a view combining data from multiple This makes triggers a useful tool for data validation and
tables. When someone inserts into the view, the enforcing custom rules.
Triggers vs Stored Procedures: 1.4 Steps to Create a Trigger
Triggers differ from stored procedures in two important PostgreSQL triggers require two parts:
ways: 1. A trigger function (with the logic to run).
1. Triggers cannot be executed manually—they 2. A trigger (which connects the function to a table
are only activated automatically by events like and defines when to run it).
INSERT, UPDATE, DELETE, or TRUNCATE.
2. Triggers cannot accept parameters, unlike Step 1: Create the Trigger Function
stored procedures which allow input arguments. Use CREATE FUNCTION to define the logic that will run
when the trigger fires.
1.2 Granularity (Row-level or Statement-level) This function must:
In PostgreSQL, there are two levels of triggers you can Return type: trigger
use: Language: usually plpgsql
1. Row-Level Triggers
These are triggered once for every row that is affected by Contain a BEGIN...END block with your logic
an event (like INSERT, UPDATE, or DELETE). When you
define a trigger "FOR EACH ROW", the trigger function Syntax example:
will be called separately for each row being changed.
For example, if an UPDATE statement affects 20 rows,
the row-level trigger will run 20 times, once for each row.
Use row-level triggers when you need to handle each
modified row individually—such as validating or modifying Step 2: Create the Trigger
data one row at a time. Use CREATE TRIGGER to:
• Name the trigger
2. Statement-Level Triggers • Define when it fires: BEFORE or AFTER
These are triggered once per SQL statement, no matter • Define what event causes it: INSERT, UPDATE,
how many rows are affected. DELETE, or TRUNCATE
• Specify the table it applies to
When you define a trigger "FOR EACH STATEMENT", it
• Set the level: FOR EACH ROW or FOR EACH
only runs once, even if the statement changes 1, 10, or
STATEMENT
1000 rows.
• Connect it to the function using EXECUTE
FUNCTION (or EXECUTE PROCEDURE in older
Use statement-level triggers when you want to perform
versions)
an action that applies to the whole operation—not
individual rows. For example, logging or summary
Syntax example:
updates.
Key Difference:
Row-level triggers: run once for each row affected.
Statement-level triggers: run once per SQL command,
regardless of the number of rows involved.
1.5 Data Auditing using Triggers
Let’s explore how to use triggers to implement data
auditing.
1.3 Trigger Table Reference
To make it easier to decide which type of trigger to use,
Scenario Overview
it’s helpful to have a reference table that shows:
We want to track and audit whenever an employee’s
• When each trigger type (BEFORE, AFTER,
employment type changes. Anytime an employee’s
INSTEAD OF) can be used employment type is updated, the system should capture
• Which events they support (INSERT, the following details:
UPDATE, DELETE, TRUNCATE) • Old and new employment type
• Whether they work at the row level or
• Old and new salary
statement level
• Timestamp of the change
This will be achieved through a trigger function, ensuring
that all changes are logged and can be audited later.
Step 1: Create Tables 1.6 Modify data at INSERT event
First, we create the employees table If you want to modify the data at the insert event (i.e.,
before the new record is inserted into the employees
table), you can create a BEFORE INSERT trigger to
modify the data before it is committed to the table.
Step 1: Create a function to modify the data before
Next, we create the employment_audit table insert
This function will allow you to modify the data (such as
applying a default salary based on the employment type
or other business logic).
Step 2: Create Trigger Function
Now, we define a trigger function
Step 2: Create a trigger to execute before insert
Now, create the trigger that will call the function before an
insert happens.
Step 3: Attach Trigger to Table
Step 3: Test the setup
We now bind the trigger function to the employees table
Now, when you insert data into the employees table
without providing a salary, it will automatically assign the
default salary based on the employment_type.
Step 4: Test the setup
Insert sample data In this case, since we didn't provide a salary, the
fn_modify_employee_data function will automatically
set the salary to 20000 for the employee with the Regular
employment type.
Update employment type
Result:
Check the audit log:
This setup should now track and log changes in both
employment type and salary for employees. You can
adjust the sample data or test different changes in the
employees table to verify that the changes are properly
captured in the employment_audit table.
1.7 Disallowing DELETE operations using Triggers Logging Deletions (Optional Approach)
By creating a trigger with a function that raises an If you want to log any deletion attempts instead of just
exception on DELETE operations, you can block delete blocking them, you can modify the function to insert
attempts from the table. records into an audit table instead of raising an exception.
• Trigger type: Use the BEFORE DELETE trigger
to stop the deletion before it actually occurs.
• Alternative Logging: Instead of blocking, you
can log deletion attempts for auditing purposes by
inserting data into a separate table.
And modify the trigger function to log the deletion attempt:
This approach ensures that your employees table data
remains intact and only allows INSERT and UPDATE
operations, aligning with your business requirements.
Step 1: Create the Employees Table
This table will store employee data, including their names,
salaries, and employment types.
Bind the modified function to the employees table:
Step 2: Create the Trigger Function to Block DELETE
This function will use a trigger to block the DELETE
operation on the employees table. 1.8 Disallowing TRUNCATE operations using
• This function raises an exception if someone tries to Triggers
delete an employee, thus preventing the deletion. In PostgreSQL, TRUNCATE is an operation that
removes all rows from a table and is much faster than a
• The RETURN NULL; statement ensures no deletion
DELETE. Since it's a statement-level operation (not row-
occurs.
level), it doesn't allow the same row-level triggers that you
would use for INSERT, UPDATE, or DELETE. That's why
we use the AFTER TRUNCATE trigger at the statement
level.
Trigger Behavior: This trigger cancels the TRUNCATE
Step 3: Create the Trigger to Bind the Function
operation by raising an exception, making sure no data is
Now, we need to create a trigger that activates before any
delete operation on the employees table, which will call deleted via the TRUNCATE command.
our fn_block_delete function.
This will ensure that the data in your employees table is
• This trigger runs before any delete operation on the
safe from being truncated, keeping it intact for further
employees table.
operations like INSERT or UPDATE.
• It calls the fn_block_delete function, which prevents
the delete by raising an exception.
Step 1: Create the Employees Table
This is the same employees table where employee
records, including salary and employment type, are
stored.
Step 4: Test the Setup
Insert some data into the employees table:
Step 2: Create the Trigger Function to Block
TRUNCATE
Attempt to delete a row from the employees table:
You will need a function that raises an exception when a
TRUNCATE operation is attempted on the employees
The DELETE operation will be blocked, and you should table.
see an error message like:
Step 3: Write the Trigger Function
This function will handle logging operations based on the
type of database event.
• The function raises an exception with a message to
inform the user that the TRUNCATE operation is not
allowed.
• RETURN NULL; ensures that the TRUNCATE
operation is canceled.
Step 3: Create the Trigger to Bind the Function
This step binds the function to the employees table, This function handles all 3 operations: INSERT, UPDATE,
specifically preventing TRUNCATE operations from being DELETE.
executed.
• AFTER TRUNCATE: This specifies that the trigger Step 4: Attach the Trigger to the Table
should fire after a TRUNCATE operation is This step binds the function to the employees table,
attempted. specifically preventing TRUNCATE operations from being
• FOR EACH STATEMENT: Since TRUNCATE is a executed.
statement-level operation (not row-level), you use
FOR EACH STATEMENT rather than FOR EACH
ROW. This trigger will run the function every time a row in the
employees table is changed.
Step 5: Test the Setup
Perform the following actions and observe the audit_log
Step 4: Test the Setup table:
Try truncating the employees table to ensure the trigger
works as expected:
Then, check your audit log:
1.9 Creating an Audit Trigger
A trigger that logs INSERT, UPDATE, and DELETE
operations from a table into an audit log table.
Step 1: Create the Main Table
Create a table named employees with the following fields:
1.10 Conditional Audit Trigger
A conditional trigger logs changes only when a
specific column is updated—for example, only when
the salary column changes.
This is the main table where changes will be tracked.
Create a trigger on the employees table that:
Step 1: Create the Audit Log Table
Create another table named audit_log to store logs of all • Logs UPDATE operations only if the salary
modifications. column is changed.
Step 1: Create the Main Table
This is the log table where all tracked changes will be
saved.
Step 2: Create the Audit Table
Step 3: Create the Trigger Function
Step 4: Create the Trigger
Step 5: Test the Setup
Insert a record:
Update that does NOT change salary:
No audit log is created, because salary didn’t change.
Update that changes salary:
Audit log is created.
Output in audit_log