SQL Triggers: Automatically React to Database Changes

A SQL trigger is code that the database automatically runs when a specific event happens, such as an INSERT, UPDATE, or DELETE.

Triggers are useful when you want the database to automatically perform an action whenever data changes  without requiring the application to remember to do it.

And we want to keep a history of every status change:

Suppose we have an orders table:

And we want to keep a history of every status change:

UPDATE orders
SET status = 'shipped'
WHERE id = 101;

For PostgreSQL:

CREATE OR REPLACE FUNCTION record_status_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.status <> NEW.status THEN
        INSERT INTO order_history (
            order_id,
            old_status,
            new_status
        )
        VALUES (
            OLD.id,
            OLD.status,
            NEW.status
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then attach the function to the table:

CREATE TRIGGER order_status_history
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION record_status_change();

Now this:

UPDATE orders
SET status = 'shipped'
WHERE id = 101;

automatically produces:

When are triggers useful?

Common examples include:

• Keeping audit/history records
• Maintaining related data
• Automatically recording timestamps
• Enforcing database-level rules
• Logging important changes

One important trade-off: triggers can hide logic inside the database, which can make an application harder to understand and debug. Use them when the rule genuinely belongs at the database level.