Why triggers are needed in PostgreSQL

Introduction

Triggers help automate actions in the database when certain events happen. They make sure important tasks run automatically without needing manual work.

Automatically update a timestamp when a record changes.

Check data rules before saving new information.

Keep related tables in sync when data changes.

Log changes made to important data for tracking.

Prevent invalid data from being added to the database.

Syntax

PostgreSQL

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

Triggers run automatically when the specified event happens on the table.

You must create a function that the trigger will call.

Examples

This trigger runs before any update on the employees table to update a timestamp column.

PostgreSQL

CREATE TRIGGER update_timestamp
  BEFORE UPDATE ON employees
  FOR EACH ROW
  EXECUTE FUNCTION update_modified_column();

This trigger runs after a row is deleted from orders to log the deletion.

PostgreSQL

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

Sample Program

This example creates a function to update a timestamp column, then a trigger that calls this function before any update on the employees table. When we update an employee's name, the modified_at column updates automatically.

PostgreSQL

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.modified_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

-- Now, when we update an employee, the modified_at column updates automatically.

UPDATE employees SET name = 'Alice' WHERE id = 1;

SELECT id, name, modified_at FROM employees WHERE id = 1;

Important Notes

Triggers can slow down database operations if overused, so use them only when needed.

Always test triggers carefully to avoid unexpected behavior.

Summary

Triggers automate tasks in the database when data changes.

They help keep data accurate and consistent without manual steps.

Triggers run functions automatically before or after data changes.