PostgreSQL – TRIGGERS

Triggers are database objects that are associated with a table and are executed automatically when certain actions (such as INSERT, UPDATE, DELETE) are performed on that table. Triggers allow you to define custom logic that will be executed before or after these actions, enabling you to enforce data integrity, maintain audit logs, and automate various tasks.

Types of Triggers: There are 2 types of Triggers in PostgreSQL database. 1. Before Trigger and 2.After Trigger.

BEFORE Trigger: Before triggers are executed before the triggering event (e.g., INSERT, UPDATE, DELETE) takes place. They can be used to modify or validate data before it’s written to the table.

AFTER Trigger: After triggers are executed after the triggering event. They can be used for tasks like logging changes or updating related tables after the main action is completed.

Creating a Trigger:

To create a trigger in PostgreSQL, you use the CREATE TRIGGER statement.

Here’s a basic syntax:

CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name FOR EACH ROW EXECUTE FUNCTION trigger_function();

Trigger Function: A trigger function is a custom PL/pgSQL function that contains the logic you want to execute when the trigger is fired. It can access the OLD and NEW record values (for UPDATE and DELETE triggers) and perform various actions based on these values.

Access to OLD and NEW Records:

OLD: In an UPDATE or DELETE trigger, OLD refers to the original values of the row being modified or deleted.

NEW: In an INSERT or UPDATE trigger, NEW refers to the new values that will be inserted or the values after the update.

Example: Let’s say you have a table named “orders” and you want to maintain an audit trail of changes using a trigger:

CREATE FUNCTION log_order_changes() RETURNS TRIGGER AS 
$$ 
BEGIN 
  IF TG_OP = 'INSERT' THEN 
       INSERT INTO order_audit (order_id, action, audit_timestamp) 
       VALUES (NEW.id, 'INSERT', NOW()); 
  ELSIF TG_OP = 'UPDATE' THEN 
       INSERT INTO order_audit (order_id, action, audit_timestamp) 
       VALUES (NEW.id, 'UPDATE', NOW()); 
  ELSIF TG_OP = 'DELETE' THEN 
     INSERT INTO order_audit (order_id, action, audit_timestamp) 
        VALUES (OLD.id, 'DELETE', NOW()); 
  END IF; 
  RETURN NEW; 
END; 
$$ LANGUAGE plpgsql; 

CREATE TRIGGER orders_audit_trigger 
AFTER INSERT OR UPDATE OR DELETE 
ON orders 
FOR EACH ROW 
EXECUTE FUNCTION log_order_changes();

Managing Triggers:

You can view the existing triggers on a table using the \d meta-command or query the pg_trigger system catalog table.