Triggers

Code

-- Audit trigger
CREATE OR REPLACE FUNCTION audit_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_table (table_name, operation, old_data, new_data, changed_at)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
        NOW()
    );
    RETURN COALESCE(NEW, OLD);
END;
$$;

-- Create trigger
CREATE TRIGGER users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_log();

-- Update timestamp trigger
CREATE OR REPLACE FUNCTION update_modified()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_modified();

Explanation

Triggers execute automatically on INSERT/UPDATE/DELETE, commonly used for audit logs and data sync.

More SQL Snippets