In this tutorial, we'll explain how triggers in PostgreSQL works. PostgreSQL triggers are database objects that automatically respond to specified events on a particular table or view. They are used to apply business rules, perform data validations, or automate tasks based on changes to the data within the database.
Triggers are defined using PL/pgSQL, which is a procedural language specifically designed for PostgreSQL. riggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.
PostgreSQL offers both per-row triggers and per-statement triggers. With a per-row trigger, the trigger function is invoked once for each row that is affected by the statement that fired the trigger. In contrast, a per-statement trigger is invoked only once when an appropriate statement is executed, regardless of the number of rows affected by that statement.
PostgreSQL triggers let programmers create rules and actions that may be carried out automatically without manual interaction, improving the efficiency of management of databases.
Components for creating triggers
- Trigger Event: Specified by a user to run the trigger function.
- Trigger Definition: Specified by a user before the function is executed.
- Trigger Function: Contains SQL statements to perform the desired action.
Trigger Function
A trigger function is a user-defined function (UDF) written in a procedural language supported by PostgreSQL (usually PL/pgSQL). This function contains the logic that will be executed. It has inbuilt CREATE SQL operation followed by the keyword FUNCTION.
CREATE OR REPLACE FUNCTION users_log_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO users_log
(old_username,old_first_name,old_last_name)
VALUES(OLD.username,OLD.first_name,OLD.last_name);
RETURN NEW; -- or RETURN NULL; for certain cases
END;
$$ LANGUAGE plpgsql;
Here we're inserting user's old data into users_log table.
Trigger Events
1. BEFORE Triggers:
- Fired before the actual operation (INSERT, UPDATE, DELETE) takes place.
- Used to modify data before it is stored in the database.
2. AFTER Triggers:
- Fired after the operation has been executed.
- Useful for tasks that should occur after the data has been modified.
Example
Here is an example of creating trigger event.
CREATE TRIGGER users_trigger
BEFORE DELETE OR UPDATE
ON public.users
FOR EACH ROW
EXECUTE PROCEDURE users_log_trigger_function();
Here, we are informing to PostgreSQL to call this trigger before a delete and update event on *users* table and link the trigger function to the trigger command. Basically, it is saying that if delete or update event happen, call *users_trigger* and execute one command that is *users_log_trigger_function*. It will insert log into *users.log* table.
Once, the delete or update event call, you can check the old values in *users_log* table.
select * from users.log
Enable/Disable Trigger
We can activate or deactivate trigger using **ENABLE** AND **DISABLE** options.
-- Disable a trigger
ALTER TABLE your_table
DISABLE TRIGGER trigger_name;
-- Enable a trigger
ALTER TABLE your_table
ENABLE TRIGGER trigger_name;
Managing Trigger
To view the existing trigger on a table, execute following command:
\d table_name
To drop a trigger, execute following command:
-- Drop a trigger
DROP TRIGGER trigger_name ON table_name;
We have seen triggers in PostgreSQL and implemented the concept. We have created trigger function and set up trigger events to perform operation. Use trigger with caution because if you configure it wrong way, it will affect on performance and unwanted complications.
