Triggers

Resources

• PostgreSQL - Create Triggers

A trigger automatically executes (or “trigger”) responses to specific events on a table or view. Triggers are used to enforce business rules, maintain data integrity, and automatically perform actions based on changes to data.

Example: Given the products table.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL,
    last_updated TIMESTAMP
);

You want the last_updated column to automatically reflect the current timestamp whenever a row in the products table is updated.

1. Create a Trigger Function

CREATE OR REPLACE FUNCTION update_last_updated()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_updated = CURRENT_TIMESTAMP;
    RETURN NEW;
END;

2. Create a Trigger

CREATE TRIGGER set_last_updated
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_last_updated();