PostgreSQL BEFORE triggers

Triggers in PostgreSQL are functions that automatically execute in response to specific events on a table, such as INSERT, UPDATE, or DELETE. A `BEFORE` trigger is fired before the event occurs, allowing you to modify the data or enforce constraints before data changes are committed.

Usage
`BEFORE` triggers are used to validate or modify data before it is inserted, updated, or deleted in a table. They are often employed for enforcing business rules, validating data integrity, or automatically updating fields.

sql
CREATE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();
Mogelijk gemaakt door

In this syntax, the `BEFORE` keyword specifies that the trigger should activate before the specified event(s) occur. `BEFORE` triggers differ from `AFTER` triggers, as `BEFORE` triggers execute prior to the event, allowing data manipulation before the transaction is finalized.

Examples

1. Basic BEFORE INSERT Trigger

sql
CREATE OR REPLACE FUNCTION check_positive_salary()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.salary < 0 THEN
    RAISE EXCEPTION 'Salary cannot be negative';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_salary_trigger
BEFORE INSERT
ON employees
FOR EACH ROW
EXECUTE FUNCTION check_positive_salary();
Mogelijk gemaakt door

This example creates a trigger that checks if the `salary` is positive before a new row is inserted into the `employees` table.

2. BEFORE UPDATE Trigger to Log Changes

sql
CREATE OR REPLACE FUNCTION log_update()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO employees_audit(employee_id, old_salary, new_salary, changed_on)
  VALUES(OLD.id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_update_trigger
BEFORE UPDATE
ON employees
FOR EACH ROW
EXECUTE FUNCTION log_update();
Mogelijk gemaakt door

Here, a trigger logs changes to the `salary` field in an `employees_audit` table before an update occurs.

3. BEFORE DELETE Trigger for Cascade Actions

sql
CREATE OR REPLACE FUNCTION prevent_deletion()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM orders WHERE orders.employee_id = OLD.id) THEN
    RAISE EXCEPTION 'Employee cannot be deleted, related orders exist';
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_deletion_trigger
BEFORE DELETE
ON employees
FOR EACH ROW
EXECUTE FUNCTION prevent_deletion();
Mogelijk gemaakt door

This trigger prevents deleting an employee if there are related orders in the `orders` table.

Tips and Best Practices

• Use descriptive trigger names. This improves readability and maintenance, making it clear what each trigger is designed to do.
• Keep trigger functions simple. Complex logic can lead to performance issues and is harder to debug.
• Test thoroughly. Ensure that triggers behave as expected in all scenarios to prevent data integrity issues.
• Document triggers. Clearly document the purpose and logic of each trigger for future reference and team collaboration.
• Consider performance impact. Triggers can affect performance; use them judiciously, especially on large tables or frequent operations.
• Understand transaction behavior. `BEFORE` triggers execute within the transaction that fired them, and changes can be rolled back if the transaction fails.
• Return the correct row. Always return `NEW` or `OLD` appropriately to ensure the trigger functions correctly.
• Disabling triggers temporarily. You can disable triggers during bulk operations or maintenance tasks using `ALTER TABLE table_name DISABLE TRIGGER trigger_name;` and re-enable them with `ALTER TABLE table_name ENABLE TRIGGER trigger_name;`.