PostgreSQL Triggers

Triggers in PostgreSQL are special functions that automatically execute in response to certain events on a table. They help automate tasks, enforce rules, and maintain data integrity without requiring manual intervention.

1. Trigger Components

• Trigger Event: The operation that activates the trigger (INSERT, UPDATE, DELETE).
• Trigger Timing: Specifies when the trigger executes (BEFORE, AFTER, INSTEAD OF).
• Trigger Function: A function that contains the logic to execute when the trigger fires.

2. Creating a Simple Trigger

-- Step 1: Create a trigger function
            CREATE OR REPLACE FUNCTION log_employee_insert()
            RETURNS TRIGGER AS $$
            BEGIN
                INSERT INTO employee_logs(employee_id, action, log_time)
                VALUES(NEW.id, 'INSERT', NOW());
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;

            -- Step 2: Create the trigger
            CREATE TRIGGER after_employee_insert
            AFTER INSERT ON employees
            FOR EACH ROW
            EXECUTE FUNCTION log_employee_insert();

3. BEFORE vs AFTER Triggers

• BEFORE: Executes before the triggering operation. Can modify the data before insert/update/delete.
• AFTER: Executes after the operation. Commonly used for logging and auditing.

4. Row-Level vs Statement-Level Triggers

• Row-Level: Executes once for each affected row. Use FOR EACH ROW.
• Statement-Level: Executes once per SQL statement, regardless of how many rows are affected. Use FOR EACH STATEMENT.

5. Trigger Example: Automatic Timestamp

-- Create function to update modified_at timestamp
            CREATE OR REPLACE FUNCTION update_modified_timestamp()
            RETURNS TRIGGER AS $$
            BEGIN
                NEW.modified_at = NOW();
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;

            -- Attach trigger BEFORE UPDATE
            CREATE TRIGGER set_modified_at
            BEFORE UPDATE ON employees
            FOR EACH ROW
            EXECUTE FUNCTION update_modified_timestamp();

6. INSTEAD OF Triggers

INSTEAD OF triggers are mainly used on views to perform operations that the view itself cannot directly execute.

-- Create a view
            CREATE VIEW active_employees AS
            SELECT id, name, department_id, salary
            FROM employees
            WHERE status = 'active';

            -- Create trigger function for the view
            CREATE OR REPLACE FUNCTION insert_active_employee()
            RETURNS TRIGGER AS $$
            BEGIN
                INSERT INTO employees(id, name, department_id, salary, status)
                VALUES (NEW.id, NEW.name, NEW.department_id, NEW.salary, 'active');
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;

            -- Attach INSTEAD OF trigger
            CREATE TRIGGER insert_on_active_employees
            INSTEAD OF INSERT ON active_employees
            FOR EACH ROW
            EXECUTE FUNCTION insert_active_employee();

7. Dropping a Trigger

-- Drop a trigger
            DROP TRIGGER IF EXISTS after_employee_insert ON employees;

            -- Drop trigger function
            DROP FUNCTION IF EXISTS log_employee_insert();

8. Best Practices

• Keep trigger logic simple and efficient to avoid performance bottlenecks.
• Use triggers for auditing, automatic updates, and enforcing complex constraints.
• Be careful with cascading triggers to prevent infinite loops.
• Document triggers clearly, especially when multiple triggers exist on the same table.
• Test triggers thoroughly in development before deploying to production.

Conclusion

Triggers in PostgreSQL are a powerful feature to automate tasks and enforce business rules. By using BEFORE, AFTER, INSTEAD OF triggers along with row-level and statement-level options, you can maintain data integrity and streamline database operations. In the next tutorial, we will explore Stored Procedures, which allow reusable batch operations and more complex logic.