PL/pgSQL BEFORE/AFTER DELETE Triggers

Master DELETE triggers — BEFORE for prevention (RAISE EXCEPTION) or soft-delete (RETURN NULL), AFTER for audit logging (capture OLD into the audit table). NEW is not available in DELETE triggers. Critical fixes: original page title said INSERT but content was DELETE, had missing space INSERT INTOemployees, stray semicolon between VALUES and RETURNING, and the AFTER trigger was bound as AFTER INSERT instead of AFTER DELETE — major copy-paste bug.

DELETE triggers fire when rows are removed from a table. The two timing flavors split common use cases cleanly:

Two important properties of DELETE triggers:

• OLD is read-only. You can't modify the row being deleted; you can only inspect it or refuse the operation.
• BEFORE DELETE returns OLD or NULL. Returning OLD continues with the deletion; returning NULL silently skips this row.

The classic protective pattern: refuse to delete certain rows entirely. Useful when business rules say a row can be archived but never removed.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id     SERIAL PRIMARY KEY,
    name   VARCHAR(100) NOT NULL,
    price  NUMERIC(10, 2) NOT NULL,
    status BOOLEAN NOT NULL DEFAULT TRUE   -- TRUE = active
);

INSERT INTO products (name, price, status) VALUES
    ('Widget A', 10.99, TRUE),
    ('Widget B', 20.49, FALSE),
    ('Widget C', 15.79, TRUE);

CREATE OR REPLACE FUNCTION prevent_active_product_deletion()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status = TRUE THEN
        RAISE EXCEPTION 'Cannot delete active product "%" (id %). Set status = FALSE first.',
                        OLD.name, OLD.id;
    END IF;

    RETURN OLD;
END;
$$;

CREATE TRIGGER products_before_delete
    BEFORE DELETE ON products
    FOR EACH ROW
    EXECUTE FUNCTION prevent_active_product_deletion();

-- Active product — should be refused
DELETE FROM products WHERE id = 1;

-- Inactive product — should succeed
DELETE FROM products WHERE id = 2;
SELECT * FROM products;

Active product blocked, inactive product gone. Even a careless DELETE FROM products with no WHERE clause would only delete the inactive ones — the trigger refuses the active rows individually, raising an exception that aborts the entire DELETE.

Another common pattern: convert a DELETE into a "mark as deleted" UPDATE. Returning NULL from a BEFORE DELETE silently skips the row's actual removal:

ALTER TABLE products
    ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP;

DROP TRIGGER IF EXISTS products_before_delete ON products;

CREATE OR REPLACE FUNCTION soft_delete_product()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Mark the row as deleted instead of actually deleting
    UPDATE products
    SET    deleted_at = CURRENT_TIMESTAMP,
           status     = FALSE
    WHERE  id = OLD.id;

    -- Returning NULL tells PostgreSQL to skip the actual DELETE
    RETURN NULL;
END;
$$;

CREATE TRIGGER products_soft_delete
    BEFORE DELETE ON products
    FOR EACH ROW
    EXECUTE FUNCTION soft_delete_product();

DELETE FROM products WHERE id = 3;
SELECT * FROM products;

The DELETE statement reports DELETE 0 — zero rows actually removed — but the row was updated in place with deleted_at filled in. The trigger silently transformed the operation.

After a row is removed, the only thing left is what was in OLD. AFTER DELETE is the right place to capture deleted data into an audit table — the row is gone from the main table, but you can preserve a record of what existed.

DROP TABLE IF EXISTS employees_audit;
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    id     SERIAL PRIMARY KEY,
    name   VARCHAR(100),
    salary NUMERIC
);

CREATE TABLE employees_audit (
    id          SERIAL PRIMARY KEY,
    employee_id INT,
    name        VARCHAR(100),
    salary      NUMERIC,
    deleted_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_by  TEXT      DEFAULT CURRENT_USER
);

INSERT INTO employees (name, salary) VALUES
    ('Alice',   50000),
    ('Bob',     60000),
    ('Charlie', 55000);

CREATE OR REPLACE FUNCTION log_employee_deletion()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees_audit (employee_id, name, salary)
    VALUES (OLD.id, OLD.name, OLD.salary);

    RETURN OLD;     -- ignored for AFTER triggers, but convention
END;
$$;

CREATE TRIGGER employees_after_delete
    AFTER DELETE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION log_employee_deletion();

DELETE FROM employees WHERE name = 'Alice';

-- Verify both tables
SELECT * FROM employees;
SELECT * FROM employees_audit;

Alice's row is gone from employees but preserved in employees_audit, complete with timestamp and the user who performed the deletion.

1. NEW doesn't exist for DELETE triggers. Don't reference it; use OLD for everything.
2. OLD is read-only. Inspect it, but don't try to assign to its fields.
3. Use AFTER DELETE for audit logging. Captures the deleted data into the log; runs only if the delete actually committed.
4. Use BEFORE DELETE + RETURN NULL for soft-delete, but document the behavior — application code may misread DELETE 0 as failure.
5. Prefer FK ON DELETE CASCADE/RESTRICT when the relationship is structural. Only use triggers when the rule is dynamic.
6. Match the event in CREATE TRIGGER to what the function does. An AFTER DELETE function bound as AFTER INSERT won't fire when you expect.

• BEFORE DELETE triggers fire before the row is removed. They can refuse the deletion (RAISE EXCEPTION) or skip it silently (RETURN NULL).
• AFTER DELETE triggers fire after the row is removed. They see only OLD; perfect for audit logging.
• NEW is never available in DELETE triggers — only OLD.
• Use BEFORE for prevention/transformation, AFTER for reaction.
• Soft-delete via RETURN NULL is a powerful trick but worth documenting — callers see DELETE 0 instead of the row count they might expect.
• For structural rules (delete restrictions, cascades), prefer foreign-key constraints over triggers.