_edited_edited.png)
In the world of databases, ensuring data integrity and automating routine tasks are crucial for maintaining efficient and reliable systems. As databases continue to grow in size and complexity, the need for effective data management tools becomes increasingly important. One such tool is the database trigger, which allows developers to define custom actions that are automatically executed in response to specific events or changes to the data. In PostgreSQL, a popular open-source relational database management system, triggers offer a powerful way to enforce data consistency, log changes, and automate tasks. In this article, we will explore the concept of PostgreSQL triggers, their types, and how to create and use them to improve database management and data integrity.
What are PostgreSQL Triggers?
PostgreSQL trigger is a set of instructions that automatically executes in response to specific events on a particular table or view in a database. These events can be actions like INSERT, UPDATE, DELETE or TRUNCATE operations.
There are two levels of Triggers
Row-level triggers: If the trigger is marked for each row, then the trigger function will be called for each row that is getting modified by the event.
Ex: If 30 rows are to be inserted into a table, the row level trigger will execute 30 times.
Statement-level triggers: For each statement option will call the trigger function only once for each statement, regardless of the number of the rows getting modified.
Ex: If 30 rows are to be inserted into a table, the statement level trigger will execute only once.
PostgreSQL supports several types of triggers, each serving different purposes based on the event and timing of execution.
Before Triggers:
These are executed before the triggering event takes place.
Validate or modify data before it’s inserted, updated or deleted.
Ex: BEFORE INSERT trigger to check if a value is valid before inserting it into the table.
After Triggers:
These are executed after the triggering event had occurred.
Log changes, update related records or perform actions after data has been inserted, updated or deleted.
Ex: AFTER UPDATE trigger to log changes to a table or update related records in another table.
Instead of Triggers:
Used with views (virtual tables based on queries).
Replaces the standard action of the triggering event, allowing for custom handling of the operation.
Customize the behavior of views, such as inserting data in to multiple tables or performing complex logic.
Ex: INSTEAD OF INSERT trigger on a view to split data into multiple tables.
Constraint Triggers:
Special type of AFTER trigger.
Can be deferred until the end of the transaction (commit or roll back).
Enforce complex constraints or rules that involve multiple tables or operations.
CONSTRAINT trigger to ensure data consistency across multiple tables.
Syntax to create a Trigger in PostgreSQL
To create a trigger in PostgreSQL, follow two steps.
1. First, create a trigger function.
The following explanation breaks down the meaning of each keyword used in the above syntax
create or replace: This keyword creates a new function or replaces an existing function.
trigger_function: This is the name of the function.
returns trigger: This keyword indicates that the function is a trigger function, which is a special type of function that is executed automatically by the database in response to certain events.
language plpgsql: This specifies the programming language used to write the function. In this case, it's PL/pgSQL, which is a procedural language extension for PostgreSQL.
Begin and end: These keywords define the start and end of the function body.
New and Old: The OLD variable represents the state of the row before the triggering event occurs, such as before an UPDATE or DELETE operation. On the other hand, the NEW variable represents the state of the row after the triggering event, such as after an INSERT or UPDATE operation.
Raise notice: These statements raise notices to display information about the trigger. PostgreSQL provides other local variables preceded by TG_ such as,
TG_NAME: provides the trigger name
TG_TABLE_NAME: provides the name of the table on which trigger is defined
TG_OP: provide the trigger operation information whether it is insert, update, delete or truncate
TG_WHEN: indicates the type of triggering event (BEFORE, AFTER or INSTEAD OF)
TG_LEVEL: indicates the level of triggering event (row level or statement level)
Return new: This keyword returns the new row that was updated, which is required for the trigger to complete successfully.
2. Second, bind the trigger function to a table
Let's take a closer look at what each keyword means in the above syntax.
create trigger: This keyword creates a new trigger.
tr_name: This is the name of the trigger.
{before | after} {event}: Triggering can be before or after an event occurs. Event can be insert, update, delete, truncate.
on table_name: Specify the table name that is associated with trigger.
[for [each] {row | statement}]: The row-level specify by FOR EACH ROW, whereas the statement-level specify by FOR EACH STATEMENT.
Steps to Create Trigger with example:
Create a new table called distributor.
Insert rows in to the table distributor.
Select * from distributor.
When the name of distributor changes, log it in a separate table called distributor_audit.
Create a trigger function.
The function inserts the name into the distributor audit table including dist_id, name and the timestamp of name of a distributor change.
Bind the trigger function to the distributor table.
Then update the distributor table.
Select * from distributor.
The output shows that ‘addition’ name has been updated with ‘gorilla’.
Select * from distributor_audit.
The change was logged in the distributor_audit table by the trigger.
This code creates a trigger that logs changes to the ‘name’ column in the ‘distributor’ table by inserting a new row in to the ‘distributor_audit’ table each time an update occurs. This allows us to keep a record of all changes made to the data.
Alter a Trigger in PostgreSQL
Drop a Trigger in PostgreSQL
Conclusion:
PostgreSQL Triggers are a powerful tool for maintaining data integrity and automating routine tasks in databases. The above example provides trigger creation, changes to a table. This highlights practical application of Triggers. By using these developers can streamline database operations, enforce data consistency, and build robust database systems that meet the demands of modern applications.
References: