PostgreSQL Triggers - Recursion Guard pg_trigger_depth()

-- ===========================================================
-- Demonstrate PostgreSQL trigger firing order and NEW visibility
-- ===========================================================

-- 1. Create a simple orders table
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_name TEXT        NOT NULL,
    total_amount  NUMERIC(10,2) NOT NULL,
    status        TEXT        NOT NULL DEFAULT 'pending',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ
);

-- 2. Create an audit log table to capture every change
CREATE TABLE order_audit_log (
    log_id        SERIAL PRIMARY KEY,
    order_id      INT,
    event_type    TEXT,         -- INSERT / UPDATE / DELETE
    old_status    TEXT,         -- NULL on INSERT
    new_status    TEXT,         -- NULL on DELETE
    changed_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
    changed_by    TEXT         NOT NULL  -- the DB role that made the change
);

-- ===========================================================
-- TRIGGER FUNCTION 1: Stamp updated_at before the row is written
-- This is a BEFORE trigger — we mutate NEW directly
-- ===========================================================
CREATE OR REPLACE FUNCTION stamp_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- NEW is the incoming row record. Mutating it here changes
    -- what actually gets written to the heap.
    NEW.updated_at := now();

    -- Returning NEW tells PostgreSQL: "use this (possibly modified)
    -- row as the actual row to insert/update".
    -- Returning NULL would cancel the operation entirely.
    RETURN NEW;
END;
$$;

-- ===========================================================
-- TRIGGER FUNCTION 2: Write an audit log entry AFTER the row lands
-- This is an AFTER trigger — OLD and NEW are both fully visible
-- ===========================================================
CREATE OR REPLACE FUNCTION log_order_status_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- On INSERT, OLD is NULL — use COALESCE to handle it safely
    INSERT INTO order_audit_log
        (order_id, event_type, old_status, new_status, changed_by)
    VALUES (
        COALESCE(NEW.order_id, OLD.order_id),
        TG_OP,                    -- built-in variable: 'INSERT','UPDATE','DELETE'
        OLD.status,               -- NULL for INSERT operations
        NEW.status,               -- NULL for DELETE operations
        current_user              -- the authenticated PostgreSQL role
    );

    -- AFTER row triggers must still return NEW (or OLD for DELETE)
    -- The return value is ignored, but NULL would suppress further
    -- triggers in the chain — always return the right record.
    RETURN NEW;
END;
$$;

-- ===========================================================
-- Attach triggers — note the naming convention: numeric prefix
-- controls alphabetical (therefore execution) order
-- ===========================================================

-- Fires BEFORE insert or update — stamps the timestamp
CREATE TRIGGER orders_10_stamp_updated_at
    BEFORE INSERT OR UPDATE
    ON orders
    FOR EACH ROW
    EXECUTE FUNCTION stamp_updated_at();

-- Fires AFTER insert, update, or delete — writes the audit log
CREATE TRIGGER orders_20_audit_status_change
    AFTER INSERT OR UPDATE OR DELETE
    ON orders
    FOR EACH ROW
    EXECUTE FUNCTION log_order_status_change();

-- ===========================================================
-- TEST IT
-- ===========================================================

-- Insert a new order
INSERT INTO orders (customer_name, total_amount)
VALUES ('Alice Johnson', 149.99);

-- Update the status
UPDATE orders
SET status = 'shipped'
WHERE customer_name = 'Alice Johnson';

-- Review the audit trail
SELECT
    log_id,
    order_id,
    event_type,
    old_status,
    new_status,
    changed_at,
    changed_by
FROM order_audit_log
ORDER BY log_id;

-- ===========================================================
-- Writable view pattern using INSTEAD OF triggers
-- Use case: e-commerce product catalog with a separate
-- inventory table — expose a unified view to the API layer
-- ===========================================================

CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    sku          TEXT   NOT NULL UNIQUE,
    product_name TEXT   NOT NULL,
    unit_price   NUMERIC(10,2) NOT NULL
);

CREATE TABLE inventory (
    inventory_id  SERIAL PRIMARY KEY,
    product_id    INT    NOT NULL REFERENCES products(product_id),
    warehouse     TEXT   NOT NULL DEFAULT 'main',
    stock_qty     INT    NOT NULL DEFAULT 0,
    UNIQUE (product_id, warehouse)
);

-- The view the application sees — a clean, flat product+stock surface
CREATE VIEW product_catalog AS
    SELECT
        p.product_id,
        p.sku,
        p.product_name,
        p.unit_price,
        i.stock_qty,
        i.warehouse
    FROM products p
    JOIN inventory i ON i.product_id = p.product_id;

-- ===========================================================
-- INSTEAD OF INSERT: fan the write out to both base tables
-- ===========================================================
CREATE OR REPLACE FUNCTION insert_into_product_catalog()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    new_product_id INT;
BEGIN
    -- Step 1: insert the core product record and capture its generated PK
    INSERT INTO products (sku, product_name, unit_price)
    VALUES (NEW.sku, NEW.product_name, NEW.unit_price)
    RETURNING product_id INTO new_product_id;

    -- Step 2: create the corresponding inventory row
    INSERT INTO inventory (product_id, warehouse, stock_qty)
    VALUES (
        new_product_id,
        COALESCE(NEW.warehouse, 'main'),  -- default to main warehouse
        COALESCE(NEW.stock_qty, 0)
    );

    -- Return NEW so PostgreSQL knows the operation "succeeded"
    RETURN NEW;
END;
$$;

-- ===========================================================
-- INSTEAD OF UPDATE: update each base table independently
-- ===========================================================
CREATE OR REPLACE FUNCTION update_product_catalog()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Only update columns that actually changed (compare OLD vs NEW)
    UPDATE products
    SET
        product_name = NEW.product_name,
        unit_price   = NEW.unit_price
    WHERE product_id = OLD.product_id;

    UPDATE inventory
    SET stock_qty = NEW.stock_qty
    WHERE product_id = OLD.product_id
      AND warehouse  = OLD.warehouse;

    RETURN NEW;
END;
$$;

-- Attach both INSTEAD OF triggers to the view
CREATE TRIGGER product_catalog_insert
    INSTEAD OF INSERT ON product_catalog
    FOR EACH ROW
    EXECUTE FUNCTION insert_into_product_catalog();

CREATE TRIGGER product_catalog_update
    INSTEAD OF UPDATE ON product_catalog
    FOR EACH ROW
    EXECUTE FUNCTION update_product_catalog();

-- ===========================================================
-- TEST: application writes to the view as if it's a table
-- ===========================================================
INSERT INTO product_catalog (sku, product_name, unit_price, stock_qty, warehouse)
VALUES ('SKU-9001', 'Wireless Keyboard', 79.99, 250, 'main');

UPDATE product_catalog
SET unit_price = 69.99, stock_qty = 230
WHERE sku = 'SKU-9001';

-- Verify data landed in the correct base tables
SELECT p.sku, p.unit_price, i.stock_qty
FROM products p JOIN inventory i ON i.product_id = p.product_id
WHERE p.sku = 'SKU-9001';

-- ===========================================================
-- High-performance bulk audit using transition tables
-- Scenario: price update job runs nightly, touching thousands
-- of product rows — we need a full audit trail without
-- 10,000 individual trigger invocations
-- ===========================================================

CREATE TABLE product_prices (
    product_id    INT  PRIMARY KEY,
    sku           TEXT NOT NULL,
    current_price NUMERIC(10,2) NOT NULL,
    updated_at    TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE price_change_audit (
    audit_id      SERIAL PRIMARY KEY,
    product_id    INT,
    sku           TEXT,
    old_price     NUMERIC(10,2),
    new_price     NUMERIC(10,2),
    price_delta   NUMERIC(10,2),   -- computed: new - old
    changed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    batch_job_id  TEXT             -- passed via SET LOCAL
);

-- ===========================================================
-- Statement-level trigger function using transition tables
-- OLD TABLE contains all rows BEFORE the UPDATE
-- NEW TABLE contains all rows AFTER the UPDATE
-- Both are queryable with standard SQL inside this function
-- ===========================================================
CREATE OR REPLACE FUNCTION audit_price_changes_bulk()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Single INSERT joining transition tables — processes ALL
    -- modified rows in one statement regardless of batch size.
    -- current_setting() reads a session-level variable set by
    -- the calling application for traceability.
    INSERT INTO price_change_audit
        (product_id, sku, old_price, new_price, price_delta, batch_job_id)
    SELECT
        old_rows.product_id,
        new_rows.sku,
        old_rows.current_price                       AS old_price,
        new_rows.current_price                       AS new_price,
        new_rows.current_price - old_rows.current_price AS price_delta,
        current_setting('app.batch_job_id', true)    -- true = return NULL if unset
    FROM old_table AS old_rows                       -- transition table: pre-update snapshot
    JOIN new_table AS new_rows                       -- transition table: post-update snapshot
      ON old_rows.product_id = new_rows.product_id
    WHERE old_rows.current_price <> new_rows.current_price;  -- only log actual changes

    -- Statement-level triggers: return value is always NULL / ignored
    RETURN NULL;
END;
$$;

-- ===========================================================
-- Attach as a statement-level AFTER UPDATE trigger
-- REFERENCING declares the transition table aliases
-- ===========================================================
CREATE TRIGGER product_prices_bulk_audit
    AFTER UPDATE ON product_prices
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT
    EXECUTE FUNCTION audit_price_changes_bulk();

-- ===========================================================
-- Seed some test data
-- ===========================================================
INSERT INTO product_prices (product_id, sku, current_price) VALUES
    (1, 'SKU-001', 29.99),
    (2, 'SKU-002', 49.99),
    (3, 'SKU-003', 99.99);

-- ===========================================================
-- Simulate a nightly batch price update
-- The app sets a session variable for traceability
-- ===========================================================
SET LOCAL app.batch_job_id = 'NIGHTLY-PRICE-JOB-2024-03-15';

UPDATE product_prices
SET
    current_price = current_price * 0.90,  -- 10% discount run
    updated_at    = now()
WHERE product_id IN (1, 2, 3);

-- One trigger invocation processed all 3 rows
SELECT
    product_id,
    sku,
    old_price,
    new_price,
    price_delta,
    batch_job_id
FROM price_change_audit
ORDER BY product_id;

-- ===========================================================
-- Pattern 1: Guard against trigger recursion using pg_trigger_depth()
-- Scenario: updating a 'categories' table that recalculates
-- a materialized path — which itself triggers the same function
-- ===========================================================

CREATE TABLE categories (
    category_id   SERIAL PRIMARY KEY,
    parent_id     INT REFERENCES categories(category_id),
    category_name TEXT NOT NULL,
    full_path     TEXT  -- e.g. 'Electronics > Laptops > Gaming'
);

CREATE OR REPLACE FUNCTION rebuild_category_path()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- pg_trigger_depth() = 0 means we're the outermost trigger call.
    -- If > 0, we're inside a recursive trigger chain — skip execution
    -- to prevent infinite recursion without raising an error.
    IF pg_trigger_depth() > 1 THEN
        RETURN NEW;
    END IF;

    -- Build the full path by recursively walking parent_id
    WITH RECURSIVE ancestor_path AS (
        -- Base case: start from the current row's parent
        SELECT parent_id, category_name, 1 AS depth
        FROM categories
        WHERE category_id = NEW.parent_id

        UNION ALL

        -- Recursive case: walk up to the root
        SELECT c.parent_id, c.category_name, ap.depth + 1
        FROM categories c
        JOIN ancestor_path ap ON ap.parent_id = c.category_id
    )
    SELECT
        string_agg(category_name, ' > ' ORDER BY depth DESC) || ' > ' || NEW.category_name
    INTO NEW.full_path
    FROM ancestor_path;

    -- If no parent exists, the category IS the root
    NEW.full_path := COALESCE(NEW.full_path, NEW.category_name);

    RETURN NEW;
END;
$$;

CREATE TRIGGER categories_rebuild_path
    BEFORE INSERT OR UPDATE OF parent_id, category_name
    ON categories
    FOR EACH ROW
    EXECUTE FUNCTION rebuild_category_path();

-- ===========================================================
-- Pattern 2: Deferred constraint trigger for referential
-- integrity that needs to survive mid-transaction inconsistency
-- Scenario: batch import that inserts child rows before parents
-- ===========================================================

CREATE TABLE departments (
    dept_id   SERIAL PRIMARY KEY,
    dept_name TEXT NOT NULL
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    full_name   TEXT NOT NULL,
    dept_id     INT  -- intentionally no FK — trigger enforces it
);

CREATE OR REPLACE FUNCTION check_dept_exists()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- This check runs at COMMIT time, not statement time,
    -- so mid-transaction inconsistency is acceptable.
    IF NOT EXISTS (
        SELECT 1 FROM departments WHERE dept_id = NEW.dept_id
    ) THEN
        RAISE EXCEPTION
            'dept_id % does not exist in departments table', NEW.dept_id;
    END IF;
    RETURN NEW;
END;
$$;

-- CONSTRAINT TRIGGER + DEFERRABLE: validation fires at end of transaction
CREATE CONSTRAINT TRIGGER employees_check_dept_deferred
    AFTER INSERT OR UPDATE OF dept_id
    ON employees
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE FUNCTION check_dept_exists();

-- ===========================================================
-- Pattern 3: Query pg_trigger to audit every trigger in the DB
-- ===========================================================
SELECT
    t.tgname                                          AS trigger_name,
    c.relname                                         AS table_name,
    p.proname                                         AS function_name,
    CASE t.tgtype & 2  WHEN 2 THEN 'BEFORE' ELSE 'AFTER'  END AS timing,
    CASE t.tgtype & 1  WHEN 1 THEN 'ROW'    ELSE 'STATEMENT' END AS scope,
    CASE
        WHEN t.tgenabled = 'O' THEN 'ENABLED'
        WHEN t.tgenabled = 'D' THEN 'DISABLED'
        WHEN t.tgenabled = 'R' THEN 'REPLICA'
        WHEN t.tgenabled = 'A' THEN 'ALWAYS'
    END                                               AS status
FROM pg_trigger t
JOIN pg_class   c ON c.oid    = t.tgrelid
JOIN pg_proc    p ON p.oid    = t.tgfoid
WHERE NOT t.tgisinternal                             -- exclude FK constraint triggers
ORDER BY c.relname, t.tgname;

-- ===========================================================
-- Secure trigger with least privilege — dedicated audit role
-- ===========================================================

-- Create a dedicated audit role with minimal permissions
CREATE ROLE trigger_audit_role;
GRANT INSERT ON TABLE order_audit_log TO trigger_audit_role;

-- Create the trigger function with SECURITY DEFINER,
-- so it runs as the owner of the function (which is the audit role)
CREATE OR REPLACE FUNCTION log_order_status_change_secure()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = 'public'   -- avoid search_path attacks
AS $$
BEGIN
    INSERT INTO order_audit_log
        (order_id, event_type, old_status, new_status, changed_by)
    VALUES (
        COALESCE(NEW.order_id, OLD.order_id),
        TG_OP,
        OLD.status,
        NEW.status,
        current_user
    );
    RETURN NEW;
END;
$$;

-- Alter the function owner to the audit role
ALTER FUNCTION log_order_status_change_secure()
    OWNER TO trigger_audit_role;

-- Attach the trigger — now the calling user only needs INSERT on orders,
-- not on the audit table. The trigger function handles audit insertion
-- as the audit role.
CREATE TRIGGER orders_20_audit_secure
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION log_order_status_change_secure();

-- ===========================================================
-- Test: user 'app_user' has INSERT on orders but not on order_audit_log
-- The trigger still works because it runs as trigger_audit_role.
-- ===========================================================

SET ROLE app_user;

INSERT INTO orders (customer_name, total_amount)
VALUES ('Bob Test', 59.99);

-- Verify audit entry exists
SELECT * FROM order_audit_log;
-- User can see the result because they have SELECT on the audit table
-- (granted separately for reporting)

RESET ROLE;

// io.thecodeforge — database tutorial

-- BEFORE: modify input before it's written
CREATE OR REPLACE FUNCTION sanitize_email_insert()
RETURNS TRIGGER AS $$
BEGIN
  NEW.email := lower(trim(NEW.email));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sanitize_email_before
  BEFORE INSERT ON user_accounts
  FOR EACH ROW
  EXECUTE FUNCTION sanitize_email_insert();

-- AFTER: log the final committed row
CREATE TABLE audit_log (
  table_name  text,
  operation   text,
  old_data    jsonb,
  new_data    jsonb,
  changed_at  timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data)
  VALUES ('user_accounts', TG_OP, row_to_json(OLD), row_to_json(NEW));
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_users
  AFTER INSERT OR UPDATE OR DELETE ON user_accounts
  FOR EACH ROW
  EXECUTE FUNCTION log_user_changes();

// io.thecodeforge — database tutorial

CREATE TABLE orders (
  id          serial PRIMARY KEY,
  customer_id integer,
  total       numeric
);

CREATE OR REPLACE FUNCTION log_before_stmt()
RETURNS TRIGGER AS $$
BEGIN
  RAISE NOTICE 'BEFORE STATEMENT: %', TG_OP;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION log_before_row()
RETURNS TRIGGER AS $$
BEGIN
  RAISE NOTICE 'BEFORE ROW: % on order %', TG_OP, NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER z_last_before_stmt
  BEFORE UPDATE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_before_stmt();

CREATE TRIGGER a_first_before_row
  BEFORE UPDATE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION log_before_row();

UPDATE orders SET total = 10 WHERE id = 1;
-- NOTICE: BEFORE STATEMENT: UPDATE (z_last says 'last' alphabetically)
-- NOTICE: BEFORE ROW: UPDATE on order 1 (a_first fires first alphabetically)

// io.thecodeforge — database tutorial

CREATE TABLE user_accounts (
  id       serial PRIMARY KEY,
  email    text,
  status   text DEFAULT 'active'
);

CREATE TABLE auth_log (
  id          serial PRIMARY KEY,
  changed_at  timestamptz DEFAULT now(),
  operation   text,
  old_email   text,
  new_email   text
);

CREATE OR REPLACE FUNCTION log_bulk_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO auth_log (operation, old_email, new_email)
  SELECT TG_OP, OLD.email, NEW.email
  FROM OLD_TABLE OLD
  JOIN NEW_TABLE NEW ON OLD.id = NEW.id;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_bulk
  AFTER UPDATE ON user_accounts
  REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_bulk_changes();

UPDATE user_accounts SET status = 'inactive' WHERE id IN (1, 2, 3);

SELECT operation, old_email, new_email FROM auth_log;
--  UPDATE | old1@x | new1@x
--  UPDATE | old2@y | new2@y
--  UPDATE | old3@z | new3@z

-- PL/pgSQL trigger function example
CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log(table_name, operation, old_data, new_data, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- C trigger function (conceptual, requires compilation)
-- CREATE OR REPLACE FUNCTION c_audit_trigger() RETURNS TRIGGER
--   AS '/path/to/audit.so', 'audit_trigger'
--   LANGUAGE C;

-- Python trigger function (requires PL/Python extension)
CREATE OR REPLACE FUNCTION py_audit_trigger() RETURNS TRIGGER AS $$
  import json
  plan = plpy.prepare("INSERT INTO audit_log(table_name, operation, old_data, new_data, changed_at) VALUES ($1, $2, $3, $4, now())", ["text", "text", "json", "json"])
  plpy.execute(plan, [TD["table_name"], TD["event"], json.dumps(TD["old"]), json.dumps(TD["new"])])
  return "MODIFY"
$$ LANGUAGE plpython3u;

-- Create audit log table
CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  table_name TEXT,
  operation TEXT,
  old_data JSONB,
  new_data JSONB,
  changed_at TIMESTAMPTZ DEFAULT now()
);

-- Statement-level trigger using transition tables
CREATE OR REPLACE FUNCTION audit_bulk_update() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log(table_name, operation, old_data, new_data)
  SELECT TG_TABLE_NAME, 'UPDATE', row_to_json(OLD), row_to_json(NEW)
  FROM old_table OLD JOIN new_table NEW USING (id);  -- assuming id is primary key
  RETURN NULL; -- statement-level trigger return value ignored
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_bulk_update
  AFTER UPDATE ON orders
  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE FUNCTION audit_bulk_update();

-- Create audit table for DDL events
CREATE TABLE ddl_audit_log (
  id SERIAL PRIMARY KEY,
  event_type TEXT,
  command_tag TEXT,
  object_type TEXT,
  object_identity TEXT,
  ddl_command TEXT,
  user_name TEXT,
  occurred_at TIMESTAMPTZ DEFAULT now()
);

-- Event trigger function
CREATE OR REPLACE FUNCTION log_ddl_events() RETURNS event_trigger AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
  LOOP
    INSERT INTO ddl_audit_log(event_type, command_tag, object_type, object_identity, ddl_command, user_name)
    VALUES (TG_EVENT, TG_TAG, r.object_type, r.object_identity, r.command, session_user);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Create event trigger
CREATE EVENT TRIGGER trg_log_ddl
  ON ddl_command_end
  EXECUTE FUNCTION log_ddl_events();