SQL Triggers — 45-Minute Cascading Deadlock

-- BEFORE INSERT trigger (PostgreSQL): normalise email case and validate
CREATE OR REPLACE FUNCTION normalise_user_before_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- Normalise email to lowercase at DB layer regardless of application input
    NEW.email = LOWER(TRIM(NEW.email));

    -- Reject obviously invalid emails at DB layer as a last defence
    IF NEW.email NOT LIKE '%@%' THEN
        RAISE EXCEPTION 'Invalid email format: %', NEW.email;
    END IF;

    RETURN NEW;  -- RETURN NEW is required in BEFORE triggers to allow the INSERT
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_normalise_user_before_insert
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION normalise_user_before_insert();

-- AFTER INSERT trigger (PostgreSQL): audit log the creation
CREATE OR REPLACE FUNCTION log_user_creation()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, changed_at, new_values)
    VALUES ('users', 'INSERT', NEW.user_id, NOW(),
            jsonb_build_object('email', NEW.email, 'created_at', NEW.created_at));
    RETURN NULL;  -- AFTER triggers: return value is ignored; use NULL
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_user_creation
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_creation();

-- Generic audit table that works for any table
CREATE TABLE audit_log (
    audit_id     BIGSERIAL PRIMARY KEY,
    table_name   VARCHAR(100)  NOT NULL,
    action       VARCHAR(10)   NOT NULL CHECK (action IN ('INSERT','UPDATE','DELETE')),
    record_id    BIGINT        NOT NULL,
    changed_by   VARCHAR(100),
    changed_at   TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    old_values   JSONB,
    new_values   JSONB
);
CREATE INDEX idx_audit_log_table_record ON audit_log(table_name, record_id);
CREATE INDEX idx_audit_log_changed_at   ON audit_log(changed_at);

-- Reusable trigger function for salary changes
CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, action, record_id, changed_by, new_values)
        VALUES ('employees', 'INSERT', NEW.employee_id,
                current_setting('app.current_user_id', true),
                to_jsonb(NEW));
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        -- Only log if salary actually changed (not every field update)
        IF OLD.salary <> NEW.salary THEN
            INSERT INTO audit_log (table_name, action, record_id, changed_by, old_values, new_values)
            VALUES ('employees', 'UPDATE', NEW.employee_id,
                    current_setting('app.current_user_id', true),
                    jsonb_build_object('salary', OLD.salary),
                    jsonb_build_object('salary', NEW.salary));
        END IF;
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, action, record_id, changed_by, old_values)
        VALUES ('employees', 'DELETE', OLD.employee_id,
                current_setting('app.current_user_id', true),
                to_jsonb(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_employees
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_employee_changes();

-- DANGEROUS: Trigger on orders that modifies customer_stats
-- which has another trigger that modifies orders -- circular!
CREATE TRIGGER trg_orders_update_stats
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION update_customer_stats();  -- updates customer_stats table

-- If customer_stats has a trigger that updates orders...
-- circular cascade -- do not do this

-- SAFE PATTERN: Use a flag to prevent recursive firing (SQL Server)
CREATE TRIGGER trg_orders_safe
AFTER INSERT ON orders
FOR EACH ROW
AS
BEGIN
    IF TRIGGER_NESTLEVEL() > 1  -- already inside a trigger call chain
        RETURN;
    -- ... safe to proceed
END;

-- PostgreSQL equivalent: check trigger nesting level
CREATE OR REPLACE FUNCTION safe_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF pg_trigger_depth() > 1 THEN
        RETURN NEW;  -- skip to prevent cascade
    END IF;
    -- ... proceed with side effect
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- List all triggers and their dependencies (PostgreSQL)
SELECT
    event_object_table   AS table_name,
    trigger_name,
    event_manipulation   AS event,
    action_timing        AS timing
FROM information_schema.triggers
WHERE trigger_schema = 'public'
ORDER BY event_object_table, action_timing;