PL/pgSQL – Triggers

A PL/pgSQL trigger is a block of PL/pgSQL code that is executed automatically in response to specific events on a table, such as INSERT, UPDATE, or DELETE operations. Triggers are often used to enforce data integrity, perform auditing, or automate complex data manipulation tasks.

Here’s the basic structure of a PL/pgSQL trigger:

CREATE TRIGGER trigger_name 
[BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON table_name 
[FOR EACH ROW] 
WHEN (condition) 
EXECUTE FUNCTION function_name();

where

• trigger_name is the name of your trigger.
• BEFORE | AFTER Specifies when the trigger should be executed. “BEFORE” triggers fire before the triggering event, while “AFTER” triggers fire after the triggering event. You choose the appropriate timing based on your requirements.
• INSERT | UPDATE | DELETE Specifies the type of operation that triggers the trigger.
• table_name is the name of the table on which the trigger is defined.
• FOR EACH ROW (optional) Indicates that the trigger operates on each row affected by the triggering event. If omitted, the trigger is a statement-level trigger that operates once for the entire query.
• WHEN (condition) (optional) Specifies a condition under which the trigger should fire. If omitted, the trigger will fire for all rows that match the trigger event.
• EXECUTE FUNCTION function_name() Specifies the PL/pgSQL function to be executed when the trigger fires. This is where you define the logic you want the trigger to perform.

Here’s an example of a simple PL/pgSQL trigger that logs changes to a “products” table:

CREATE OR REPLACE FUNCTION log_product_changes() 
RETURNS TRIGGER AS $$ 
BEGIN 
     IF TG_OP = 'INSERT' THEN INSERT INTO product_audit (action, product_id, product_name) VALUES ('INSERT', NEW.id, NEW.name); 
     ELSIF TG_OP = 'UPDATE' THEN INSERT INTO product_audit (action, product_id, product_name) VALUES ('UPDATE', NEW.id, NEW.name); 
     ELSIF TG_OP = 'DELETE' THEN INSERT INTO product_audit (action, product_id, product_name) VALUES ('DELETE', OLD.id, OLD.name); 
     END IF; 
     RETURN NEW; 
END; 
$$ LANGUAGE plpgsql; 

CREATE TRIGGER product_change_trigger 
AFTER INSERT OR UPDATE OR DELETE ON products 
FOR EACH ROW 
EXECUTE FUNCTION log_product_changes();

In this example:

• We defined a PL/pgSQL function called log_product_changes that inserts audit records into a “product_audit” table based on the trigger event (INSERT, UPDATE, or DELETE).
• We created an AFTER trigger named product_change_trigger that fires for each row affected by INSERT, UPDATE, or DELETE operations on the “products” table. The trigger calls the log_product_changes function.

When an INSERT, UPDATE, or DELETE operation is performed on the “products” table, the trigger will log the action and affected row data to the “product_audit” table.

PL/pgSQL triggers offer great flexibility and power for automating actions in response to database events, but they should be used carefully to avoid unintended side effects and performance issues.