Advanced Data Modelling - Triggers in PostgreSQL

Introduction

Triggers are special types of stored procedures in PostgreSQL that automatically execute when certain events occur in the database. They are useful for enforcing business rules, auditing changes, and maintaining data integrity. This tutorial covers creating, managing, and using triggers in PostgreSQL.

Creating Triggers

Triggers are created using the CREATE TRIGGER statement. Here is an example:

CREATE OR REPLACE FUNCTION audit_log()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_table (table_name, action, change_date)
    VALUES (TG_TABLE_NAME, TG_OP, now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_insert_trigger
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_log();
                    

This example creates a trigger named after_insert_trigger that logs insert operations on the employees table into an audit_table.

Trigger Events

Triggers can be fired before or after events such as INSERT, UPDATE, DELETE, or TRUNCATE. Here are examples of different trigger events:

CREATE TRIGGER before_update_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_log();

CREATE TRIGGER after_delete_trigger
AFTER DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION delete_log();
                    

These examples create triggers that execute functions update_log() and delete_log() before updating and after deleting rows from the employees table, respectively.

Accessing Trigger Data

Triggers can access data related to the triggering event using special variables like TG_TABLE_NAME and NEW. Here is an example:

CREATE OR REPLACE FUNCTION update_log()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_table (table_name, action, change_date)
    VALUES (TG_TABLE_NAME, 'UPDATE', now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
                    

This function logs updates on any table into the audit_table using the TG_TABLE_NAME variable.

Dropping Triggers

Triggers can be removed using the DROP TRIGGER statement. Here is an example:

DROP TRIGGER after_insert_trigger ON employees;
                    

This statement drops the after_insert_trigger on the employees table.

Best Practices

Here are some best practices for using triggers in PostgreSQL:

• Use triggers sparingly for critical business logic to avoid complex dependencies.
• Keep trigger logic simple and efficient to minimize impact on database performance.
• Document triggers thoroughly, including their purpose and behavior.
• Regularly review and maintain triggers to ensure they align with evolving business requirements.