SQL Triggers — 45-Minute Cascading Deadlock

Every production database eventually reaches a moment where you need something to happen automatically when data changes — log who changed a salary record, prevent a negative inventory count, or replicate a row to an audit table without trusting every application developer to remember. Triggers are the database's mechanism for this.

But triggers are one of the most misused features in SQL. They execute invisibly to the calling application, complicate debugging, can cascade into catastrophic chains, and can silently degrade INSERT/UPDATE/DELETE performance when they grow complex. Understanding their mechanics — and their failure modes — is what separates engineers who use them appropriately from those who create unmaintainable databases.

Why SQL Triggers Are a Double-Edged Sword

An SQL trigger is a procedural block that automatically executes in response to a data change event — INSERT, UPDATE, or DELETE — on a specific table. Unlike constraints or application-level logic, triggers live inside the database engine and fire within the same transaction as the triggering statement. This means they inherit the transaction's locks, isolation level, and rollback scope, making them invisible but powerful modifiers of data integrity.

Triggers execute either BEFORE or AFTER the triggering event, and can be row-level (once per affected row) or statement-level (once per SQL statement). Row-level triggers are O(n) relative to the number of rows modified, which matters when a single UPDATE touches 100,000 rows — each trigger invocation adds latency and lock duration. Triggers also have access to OLD and NEW pseudo-rows, enabling audit trails, derived column updates, or cascading changes without application code.

Use triggers when you must enforce business rules that span tables and cannot be deferred to the application — for example, maintaining a materialized aggregate count, logging all deletions for compliance, or preventing orphaned rows in a denormalized schema. Avoid them for logic that can be expressed as a CHECK constraint, a foreign key with CASCADE, or a computed column. In production, triggers are often the root cause of deadlocks and unexpected write amplification because they execute under the caller's transaction context, silently extending lock duration.

BEFORE vs AFTER Triggers — Choosing the Right Timing

A BEFORE trigger fires before the DML operation executes. You can inspect and modify the incoming row values, or raise an error to reject the operation entirely. BEFORE triggers are the right tool for input validation and data normalisation that must happen at the database layer.

An AFTER trigger fires after the DML operation has succeeded. The row is already in the table when the trigger runs. AFTER triggers are the right tool for audit logging, cascading updates to related tables, and any side effect that should only happen if the primary operation succeeded.

In SQL Server, the equivalent of BEFORE triggers are INSTEAD OF triggers — they replace the DML operation entirely. You must explicitly perform the INSERT, UPDATE, or DELETE inside the trigger body if you want the original operation to proceed. They are most commonly used on views to make views updatable.

-- 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();

Audit Logging with Triggers — The Production Pattern

Audit logging is the most legitimate and common trigger use case. The requirement: every change to sensitive data (salaries, prices, access permissions) must be recorded with who changed it, when, what it was before, and what it became after. Doing this reliably at the application layer is fragile — developers forget to add logging to new code paths. A trigger guarantees it happens regardless of how the data change occurs.

The standard pattern uses the INSERTED and DELETED virtual tables in SQL Server, or the OLD and NEW row references in PostgreSQL and MySQL. These give you the pre- and post-change values inside the trigger body. Store them in a generic audit table with a JSONB column (PostgreSQL) or a structured audit table with before/after value columns.

-- 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();

Cascading Triggers and When NOT to Use Triggers

A cascading trigger occurs when Trigger A fires a DML operation that activates Trigger B, which in turn fires Trigger C, and so on. Most databases allow cascading triggers up to a configured depth (SQL Server defaults to 32 levels). Cascades make debugging a nightmare: a single INSERT can cause dozens of side effects that are invisible to the calling application and to monitoring tools that track queries.

The worst cascading scenario is a trigger cycle: Trigger A on table T1 updates table T2, Trigger B on T2 updates T1, which fires Trigger A again — an infinite loop that runs until the recursion depth limit throws an error. This type of bug is introduced gradually as triggers are added over time without a dependency map.

Business logic does not belong in triggers. Logic like 'when an order is placed, send a welcome email' or 'recalculate the customer's tier based on total spend' has external dependencies, needs to be tested independently, and needs to be deployed with the rest of the application code. Putting it in a trigger makes it invisible, hard to test, and impossible to roll back independently of the database schema.

-- 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;

Row-Level vs Statement-Level Triggers — The Hidden Performance Killer

Most developers don't know there are two distinct execution modes for triggers. Row-level triggers fire once per affected row. Statement-level triggers fire once per SQL statement regardless of how many rows changed. The difference will destroy your production database if you get it wrong. Imagine an UPDATE that touches 100,000 rows. A row-level trigger runs 100,000 times. A statement-level trigger runs once. That's the difference between a 50ms operation and a 5-minute outage. MySQL only supports row-level triggers. PostgreSQL and Oracle give you both. SQL Server defaults to statement-level. Always check your dialect's default. Batch operations with row-level triggers are the #1 cause of unexpected performance degradation in trigger-heavy systems. Test with realistic dataset sizes, not your 12-row development table. Profile execution plans. That row-level audit trigger you wrote yesterday? It's now the bottleneck for your nightly bulk update job.

// io.thecodeforge
-- PostgreSQL: Statement-level trigger for batch operations
-- Note: No FOR EACH ROW clause makes this statement-level

CREATE OR REPLACE FUNCTION log_bulk_update()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (event_type, affected_rows, timestamp)
    VALUES ('BULK_UPDATE', TG_NARG, NOW());
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_bulk_update_audit
AFTER UPDATE ON orders
-- No FOR EACH ROW → fires once per statement
EXECUTE FUNCTION log_bulk_update();

Error Handling and Transaction Safety — Don't Let a Trigger Take Down a Billion-Dollar Transaction

Triggers execute inside the calling transaction. A trigger failure rolls back the entire transaction. That sounds safe, but here's the reality: a poorly handled validation trigger on a customers table can cancel a payments batch, a user registration, and a subscription renewal in one atomic failure. Your trigger's error handling must be bulletproof. Never catch exceptions silently. Always use SIGNAL or RAISE with descriptive messages. Store trigger-specific errors in a separate error log table — not the main application log. Design for idempotency. Triggers that run multiple times (replays, retries) should produce the same result. Test with concurrent writes. Race conditions in triggers cause deadlocks. Put a timeout on trigger execution. Fifteen seconds is the absolute max for customer-facing operations. Document every trigger with its transaction boundary implications. Your team needs to know: 'This trigger on the orders table can take down the entire checkout flow if it fails.'

// io.thecodeforge
-- MySQL/MariaDB: Safe trigger with error logging

CREATE TABLE trigger_error_log (
    error_id INT AUTO_INCREMENT PRIMARY KEY,
    error_message TEXT,
    table_name VARCHAR(100),
    error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_prevent_overdraft
BEFORE INSERT ON transactions
FOR EACH ROW
BEGIN
    DECLARE current_balance DECIMAL(10,2);
    
    SELECT balance INTO current_balance
    FROM accounts WHERE account_id = NEW.account_id
    FOR UPDATE;  -- Prevent race condition
    
    IF (current_balance - NEW.amount) < -1000 THEN
        INSERT INTO trigger_error_log (
            error_message, table_name
        ) VALUES (
            CONCAT('Overdraft limit exceeded for account '
                   , NEW.account_id, '. Balance: '
                   , current_balance, ', Amount: ', NEW.amount),
            'transactions'
        );
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Overdraft limit of -1000 exceeded';
    END IF;
END;