PostgreSQL — Triggers

Basic Trigger

-- Create trigger function
CREATE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER trigger_update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Audit Trigger

CREATE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, changed_at)
    VALUES (TG_TABLE_NAME, OLD.id, TG_OP, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_changes();

Trigger Events

Event	Description
BEFORE	Before operation
AFTER	After operation
INSTEAD OF	Replace operation

Disable Trigger

ALTER TABLE users DISABLE TRIGGER trigger_name;
ALTER TABLE users ENABLE TRIGGER trigger_name;

Drop Trigger

DROP TRIGGER trigger_name ON users;

Mini Practice

1. Create a trigger
2. Use audit logging
3. Disable a trigger
4. Drop a trigger

Up Next

Continue with Transactions — transaction management.