PostgreSQL Row-level Triggers

Triggers in PostgreSQL are procedures that are automatically executed in response to certain events on a particular table or view. Row-level triggers are specifically executed for each row affected by an event such as an `INSERT`, `UPDATE`, or `DELETE`.

Usage

Row-level triggers are utilized to enforce business rules, validate data, or maintain custom logging at the row level. They are defined to perform specific actions whenever a row is modified in a specified manner.

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

In this syntax, `FOR EACH ROW` specifies that the trigger will fire for each affected row, and `EXECUTE FUNCTION` calls the function that contains the logic to be executed.

Examples

1. Basic Insert Trigger

sql
CREATE OR REPLACE FUNCTION log_new_inserts()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, timestamp)
  VALUES (TG_TABLE_NAME, 'INSERT', now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_insert
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_new_inserts();

This example creates a trigger that logs every new `INSERT` operation on the `employees` table to an `audit_log` table.

2. Update Trigger with Condition

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

CREATE TRIGGER salary_check
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary_update();

Here, a trigger prevents salary reductions by raising an exception if the new salary is less than the old one during an `UPDATE`.

3. Delete Trigger with Logging

sql
CREATE OR REPLACE FUNCTION log_delete()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO delete_log (table_name, deleted_row_id, timestamp)
  VALUES (TG_TABLE_NAME, OLD.id, now());
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_delete
AFTER DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_delete();

This example logs details of deleted rows from the `orders` table into a `delete_log` for audit purposes. It assumes `OLD.id` corresponds to the primary key or a unique identifier of the row.

Tips and Best Practices

• Use triggers judiciously. Overusing triggers may lead to complex and hard-to-debug systems.
• Ensure atomicity. The trigger function should be atomic and idempotent to maintain data integrity.
• Consider performance impacts. Triggers add overhead and are executed synchronously with the triggering operation; ensure they are optimized for performance to avoid slowing down database operations.
• Test triggers thoroughly. Ensure comprehensive testing to validate that triggers behave correctly under all scenarios.
• Document trigger logic. Clearly document the purpose and logic of each trigger for future reference and maintenance.

Additional Information

Special Variables: PostgreSQL triggers use special variables like `TG_TABLE_NAME`, `NEW`, and `OLD` to provide context. `TG_TABLE_NAME` refers to the name of the table the trigger is associated with, `NEW` holds the new row data for `INSERT` or `UPDATE` operations, and `OLD` holds the existing row data for `UPDATE` or `DELETE` operations.