Automatically execute SQL on table events.
In Postgres, a trigger executes a set of actions automatically on table events such as INSERTs, UPDATEs, DELETEs, or TRUNCATE operations.
Creating a Trigger#
Creating triggers involve 2 parts:
- A Function which will be executed (called the Trigger Function)
- The actual Trigger object, with parameters around when the trigger should be run.
An example of a trigger is:
_10create trigger "trigger_name"_10after insert on "table_name"_10for each row_10execute statement trigger_function();
A trigger function is a user-defined Function that Postgres executes when the trigger is fired.
Example trigger function#
Here is an example that updates
salary_log whenever an employee's salary is updated:
_16-- Example: Update salary_log when salary is updated_16create function update_salary_log()_16returns trigger_16language plpgsql_16as $$_16begin_16insert into salary_log(employee_id, old_salary, new_salary)_16values (new.id, old.salary, new.salary);_16return new;_16end;_16$$;_16_16create trigger salary_update_trigger_16after update on employees_16for each row_16execute function update_salary_log();
Trigger functions have access to several special variables that provide information about the context of the trigger event and the data being modified. In the example above you can see the values inserted into the salary log are
new.salary - in this case
old specifies the previous values and
new specifies the updated values.
Here are some of the key variables and options available within trigger functions:
TG_NAME: The name of the trigger being fired.
TG_WHEN: The timing of the trigger event (
TG_OP: The operation that triggered the event (
OLD: A record variable holding the old row's data in
NEW: A record variable holding the new row's data in
TG_LEVEL: The trigger level (
STATEMENT), indicating whether the trigger is row-level or statement-level.
TG_RELID: The object ID of the table on which the trigger is being fired.
TG_TABLE_NAME: The name of the table on which the trigger is being fired.
TG_TABLE_SCHEMA: The schema of the table on which the trigger is being fired.
TG_ARGV: An array of string arguments provided when creating the trigger.
TG_NARGS: The number of arguments in the
Types of Triggers#
There are two types of trigger,
Trigger before changes are made#
Executes before the triggering event.
_10create trigger before_insert_trigger_10before insert on orders_10for each row_10execute statement before_insert_function();
Trigger after changed are made#
Executes after the triggering event.
_10create trigger after_delete_trigger_10after delete on customers_10for each row_10execute statement after_delete_function();
There are two options available for executing triggers:
for each row: specifies that the trigger function should be executed once for each affected row.
for each statement: the trigger is executed once for the entire operation (eg, once on insert). This can be more efficient than
for each rowwhen dealing with multiple rows affected by a single SQL statement, as they allow you to perform calculations or updates on groups of rows at once.
Dropping a Trigger#
You can delete a trigger using the
drop trigger command:
_10drop trigger "trigger_name" on "table_name";