SQL 87 🛢️ Database Triggers: BEFORE, AFTER, and INSTEAD OF - KandZ

⏱️ Estimated read: 19 min | |

A trigger is a piece of code the database runs automatically when a table changes. The application does not call it; the INSERT, the UPDATE, or the DELETE calls it. The trigger is the mechanism for the rules that must hold regardless of which application or which developer performs the change. The audit log, the computed column, the validation, the cascading update, and the view modification are the classic uses. The three timing options are BEFORE, AFTER, and INSTEAD OF, and each has a different role: the BEFORE validates or modifies the row before it is written, the AFTER records or cascades after the change is committed, and the INSTEAD OF intercepts the operation on a view and replaces it with the underlying table work.

Key point: A BEFORE trigger can modify the row being written, and the modification is what the database stores. An AFTER trigger cannot modify the row, and it runs after the change is visible. An INSTEAD OF trigger is only on a view, and it replaces the default action entirely.

Why triggers exist

The consistency problem. A rule must hold for every change, regardless of the code path. The application validates the input, but the bulk import, the migration script, and the ad-hoc update do not. The trigger enforces the rule at the database, and the rule holds for every path.

The audit problem. The record of who changed what and when must be accurate. The application-level audit is easy to forget, and the direct SQL bypasses it. The AFTER trigger writes the audit row for every change, and the trail is complete.

The computed-column problem. A derived column must stay in sync. The updated_at timestamp, the normalized email, the full name, the search vector. The BEFORE trigger computes the value, and the column is correct without the application’s help.

The cascade problem. A change to one table must propagate to another. The delete of the parent must delete the children, or the order total must update the summary. The AFTER trigger propagates the change.

The view-modification problem. A view that joins several tables is not directly updatable. The INSTEAD OF trigger intercepts the INSERT, the UPDATE, or the DELETE on the view and applies the changes to the underlying tables.

The constraint problem. A complex rule that the CHECK constraint cannot express. The cross-row rule, the cross-table rule, the temporal rule. The BEFORE trigger raises the exception, and the rule holds.

The performance problem. The trigger runs inside the transaction, and the work is part of the change. The application does not make a separate round trip, and the change is atomic with the trigger’s work.

a. The BEFORE triggers

The BEFORE trigger runs before the row is written. It can read the old and the new values, and it can modify the new value. The modification is what the database stores.

The PostgreSQL BEFORE trigger

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

The NEW.updated_at := NOW() sets the value, and the RETURN NEW returns the modified row. The database stores the modified row.

The SQL Server INSTEAD OF and the AFTER

SQL Server has the INSTEAD OF and the AFTER triggers, and it does not have the BEFORE. The INSTEAD OF on a table is the closest alternative.

CREATE TRIGGER trg_set_updated_at
ON orders
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE orders
    SET updated_at = GETDATE()
    FROM inserted i
    WHERE orders.id = i.id;
END;

The INSTEAD OF trigger intercepts the update and performs the modified update.

The MySQL BEFORE trigger

DELIMITER //

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //

DELIMITER ;

The SET NEW.updated_at = NOW() sets the value, and the database stores it.

The validation trigger

CREATE OR REPLACE FUNCTION validate_order()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.total < 0 THEN
        RAISE EXCEPTION 'The total must be non-negative'
            USING ERRCODE = '23514';
    END IF;

    IF NEW.customer_id IS NULL THEN
        RAISE EXCEPTION 'The customer is required'
            USING ERRCODE = '23502';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_order
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION validate_order();

The RAISE EXCEPTION aborts the change, and the trigger enforces the rule.

The BEFORE trigger table

Database	Syntax
PostgreSQL	`BEFORE UPDATE ... EXECUTE FUNCTION`
SQL Server	The `INSTEAD OF` alternative
MySQL	`BEFORE UPDATE ON ... FOR EACH ROW`
Oracle	`BEFORE UPDATE ON ... FOR EACH ROW`

The OLD and the NEW

The BEFORE trigger reads the OLD and the NEW. The OLD is the row before the change, and the NEW is the row being written.

Operation	OLD	NEW
`INSERT`	null	the new row
`UPDATE`	the old row	the new row
`DELETE`	the old row	null

The BEFORE trigger modifies the NEW for the INSERT and the UPDATE. For the DELETE, the NEW is not available, and the OLD is the row being removed.

b. The AFTER triggers

The AFTER trigger runs after the row is written. The change is visible, and the trigger cannot modify the row. It reads the OLD and the NEW and performs the follow-up work.

The audit trigger

CREATE OR REPLACE FUNCTION audit_order_change()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO order_audit (order_id, action, new_total, changed_by, changed_at)
        VALUES (NEW.id, 'INSERT', NEW.total, current_user, NOW());
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO order_audit (order_id, action, old_total, new_total, changed_by, changed_at)
        VALUES (NEW.id, 'UPDATE', OLD.total, NEW.total, current_user, NOW());
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO order_audit (order_id, action, old_total, changed_by, changed_at)
        VALUES (OLD.id, 'DELETE', OLD.total, current_user, NOW());
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_order
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION audit_order_change();

The TG_OP is the operation. The AFTER trigger writes the audit row for the three operations.

The `TG_OP` and the trigger context

Variable	Purpose
`TG_OP`	The operation (`INSERT`, `UPDATE`, `DELETE`)
`TG_TABLE_NAME`	The table name
`TG_WHEN`	The timing (`BEFORE`, `AFTER`, `INSTEAD OF`)
`OLD`	The old row
`NEW`	The new row

The cascade trigger

CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders
    SET total = (
        SELECT COALESCE(SUM(quantity * price), 0)
        FROM order_items oi
        JOIN products p ON p.id = oi.product_id
        WHERE oi.order_id = COALESCE(NEW.order_id, OLD.order_id)
    )
    WHERE id = COALESCE(NEW.order_id, OLD.order_id);

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_order_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_order_total();

The trigger recomputes the order total when the line items change.

The SQL Server AFTER trigger

CREATE TRIGGER trg_audit_order
ON orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO order_audit (order_id, action, changed_by, changed_at)
    SELECT
        COALESCE(i.id, d.id),
        CASE
            WHEN i.id IS NOT NULL AND d.id IS NULL THEN 'INSERT'
            WHEN i.id IS NOT NULL AND d.id IS NOT NULL THEN 'UPDATE'
            WHEN i.id IS NULL AND d.id IS NOT NULL THEN 'DELETE'
        END,
        SUSER_SNAME(),
        GETDATE()
    FROM inserted i
    FULL OUTER JOIN deleted d ON i.id = d.id;
END;

The inserted and the deleted are the SQL Server pseudo-tables. The inserted holds the new rows, and the deleted holds the old rows.

The MySQL AFTER trigger

DELIMITER //

CREATE TRIGGER trg_audit_order
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, action, old_total, new_total, changed_at)
    VALUES (NEW.id, 'UPDATE', OLD.total, NEW.total, NOW());
END //

DELIMITER ;

The MySQL AFTER trigger runs once per row with the FOR EACH ROW.

The AFTER trigger table

Database	Syntax
PostgreSQL	`AFTER INSERT OR UPDATE ... EXECUTE FUNCTION`
SQL Server	`AFTER INSERT, UPDATE, DELETE AS ...`
MySQL	`AFTER UPDATE ON ... FOR EACH ROW`
Oracle	`AFTER UPDATE ON ... FOR EACH ROW`

c. The INSTEAD OF triggers

The INSTEAD OF trigger is only on a view. It intercepts the INSERT, the UPDATE, or the DELETE on the view and replaces the default action.

The view-modification problem

A view that joins the users and the profiles is not directly updatable. The INSERT into the view is ambiguous: which table receives the row? The INSTEAD OF trigger defines the mapping.

CREATE VIEW user_profiles AS
SELECT u.id, u.email, p.bio, p.avatar_url
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id;

The view joins the two tables, and the insert must be split.

The INSTEAD OF INSERT trigger

CREATE OR REPLACE FUNCTION insert_user_profile()
RETURNS TRIGGER AS $$
DECLARE
    v_user_id INT;
BEGIN
    INSERT INTO users (email) VALUES (NEW.email)
    RETURNING id INTO v_user_id;

    INSERT INTO profiles (user_id, bio, avatar_url)
    VALUES (v_user_id, NEW.bio, NEW.avatar_url);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_insert_user_profile
INSTEAD OF INSERT ON user_profiles
FOR EACH ROW
EXECUTE FUNCTION insert_user_profile();

The trigger splits the insert into the two underlying tables.

The INSTEAD OF UPDATE trigger

CREATE OR REPLACE FUNCTION update_user_profile()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE users SET email = NEW.email WHERE id = OLD.id;
    UPDATE profiles SET bio = NEW.bio, avatar_url = NEW.avatar_url
    WHERE user_id = OLD.id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_user_profile
INSTEAD OF UPDATE ON user_profiles
FOR EACH ROW
EXECUTE FUNCTION update_user_profile();

The trigger applies the update to the two tables.

The INSTEAD OF DELETE trigger

CREATE OR REPLACE FUNCTION delete_user_profile()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM profiles WHERE user_id = OLD.id;
    DELETE FROM users WHERE id = OLD.id;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_delete_user_profile
INSTEAD OF DELETE ON user_profiles
FOR EACH ROW
EXECUTE FUNCTION delete_user_profile();

The trigger deletes the rows from the two tables.

The SQL Server INSTEAD OF trigger

CREATE TRIGGER trg_insert_user_profile
ON user_profiles
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @user_id INT;

    INSERT INTO users (email)
    SELECT email FROM inserted;

    SET @user_id = SCOPE_IDENTITY();

    INSERT INTO profiles (user_id, bio, avatar_url)
    SELECT @user_id, bio, avatar_url FROM inserted;
END;

The SQL Server INSTEAD OF trigger uses the inserted pseudo-table.

The INSTEAD OF trigger table

Database	Syntax
PostgreSQL	`INSTEAD OF INSERT ON view ...`
SQL Server	`INSTEAD OF INSERT ON view AS ...`
MySQL	Not supported
Oracle	`INSTEAD OF INSERT ON view ...`

Complete Example Session

-- ============================================
-- PART 1: THE BEFORE TRIGGER
-- ============================================

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- ============================================
-- PART 2: THE VALIDATION TRIGGER
-- ============================================

CREATE OR REPLACE FUNCTION validate_order()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.total < 0 THEN
        RAISE EXCEPTION 'The total must be non-negative' USING ERRCODE = '23514';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_order
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION validate_order();

-- ============================================
-- PART 3: THE AUDIT TRIGGER
-- ============================================

CREATE OR REPLACE FUNCTION audit_order_change()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO order_audit (order_id, action, new_total, changed_at)
        VALUES (NEW.id, 'INSERT', NEW.total, NOW());
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO order_audit (order_id, action, old_total, new_total, changed_at)
        VALUES (NEW.id, 'UPDATE', OLD.total, NEW.total, NOW());
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO order_audit (order_id, action, old_total, changed_at)
        VALUES (OLD.id, 'DELETE', OLD.total, NOW());
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_order
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION audit_order_change();

-- ============================================
-- PART 4: THE CASCADE TRIGGER
-- ============================================

CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders
    SET total = (
        SELECT COALESCE(SUM(quantity * price), 0)
        FROM order_items oi
        JOIN products p ON p.id = oi.product_id
        WHERE oi.order_id = COALESCE(NEW.order_id, OLD.order_id)
    )
    WHERE id = COALESCE(NEW.order_id, OLD.order_id);

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_order_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_order_total();

-- ============================================
-- PART 5: THE INSTEAD OF TRIGGER
-- ============================================

CREATE VIEW user_profiles AS
SELECT u.id, u.email, p.bio, p.avatar_url
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id;

CREATE OR REPLACE FUNCTION insert_user_profile()
RETURNS TRIGGER AS $$
DECLARE
    v_user_id INT;
BEGIN
    INSERT INTO users (email) VALUES (NEW.email)
    RETURNING id INTO v_user_id;

    INSERT INTO profiles (user_id, bio, avatar_url)
    VALUES (v_user_id, NEW.bio, NEW.avatar_url);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_insert_user_profile
INSTEAD OF INSERT ON user_profiles
FOR EACH ROW
EXECUTE FUNCTION insert_user_profile();

-- ============================================
-- PART 6: THE SQL SERVER AFTER TRIGGER
-- ============================================

CREATE TRIGGER trg_audit_order
ON orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO order_audit (order_id, action, changed_at)
    SELECT
        COALESCE(i.id, d.id),
        CASE
            WHEN i.id IS NOT NULL AND d.id IS NULL THEN 'INSERT'
            WHEN i.id IS NOT NULL AND d.id IS NOT NULL THEN 'UPDATE'
            WHEN i.id IS NULL AND d.id IS NOT NULL THEN 'DELETE'
        END,
        GETDATE()
    FROM inserted i
    FULL OUTER JOIN deleted d ON i.id = d.id;
END;

-- ============================================
-- PART 7: THE MYSQL BEFORE TRIGGER
-- ============================================

DELIMITER //

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //

DELIMITER ;

-- ============================================
-- PART 8: THE ENABLE AND DISABLE
-- ============================================

ALTER TABLE orders DISABLE TRIGGER trg_audit_order;
ALTER TABLE orders ENABLE TRIGGER trg_audit_order;

-- ============================================
-- PART 9: THE DROP
-- ============================================

DROP TRIGGER trg_audit_order ON orders;
DROP FUNCTION audit_order_change();

-- ============================================
-- PART 10: THE COMPLETE PATTERN
-- ============================================

-- 1. Use the BEFORE trigger to validate or modify the row.
-- 2. Use the AFTER trigger to audit or cascade.
-- 3. Use the INSTEAD OF trigger to modify a view.
-- 4. Use the NEW and the OLD to read the values.
-- 5. Use the TG_OP to detect the operation.
-- 6. Use the RAISE EXCEPTION to reject the change.
-- 7. Use the FOR EACH ROW for the row-level trigger.
-- 8. Use the FOR EACH STATEMENT for the statement-level.
-- 9. Disable the trigger during the bulk load.
-- 10. Document the triggers and their purpose.

The ten parts showed the BEFORE trigger, the validation trigger, the audit trigger, the cascade trigger, the INSTEAD OF trigger, the SQL Server AFTER trigger, the MySQL BEFORE trigger, the enable and disable, the drop, and the complete pattern.

Quick Reference

The Trigger Timing

Timing	Runs	Can modify
`BEFORE`	Before the row is written	Yes
`AFTER`	After the row is written	No
`INSTEAD OF`	Instead of the operation	Yes (on a view)

The Trigger Events

Event	The OLD	The NEW
`INSERT`	null	the new row
`UPDATE`	the old row	the new row
`DELETE`	the old row	null

The Trigger Variables

Variable	Purpose
`NEW`	The new row
`OLD`	The old row
`TG_OP`	The operation
`TG_TABLE_NAME`	The table
`TG_WHEN`	The timing

The Trigger Syntax

Database	The `BEFORE`
PostgreSQL	`BEFORE UPDATE ... EXECUTE FUNCTION`
SQL Server	The `INSTEAD OF` alternative
MySQL	`BEFORE UPDATE ON ... FOR EACH ROW`

The Common Uses

Use case	The timing
The `updated_at`	`BEFORE`
The validation	`BEFORE`
The audit	`AFTER`
The cascade	`AFTER`
The view insert	`INSTEAD OF`

Best Practices

✅ Do This:

-- Use the BEFORE trigger for the validation.
CREATE TRIGGER trg_validate BEFORE INSERT ON orders FOR EACH ROW ...;  -- ✅

-- Use the AFTER trigger for the audit.
CREATE TRIGGER trg_audit AFTER INSERT OR UPDATE OR DELETE ON orders ...;  -- ✅

-- Use the NEW and the OLD.
NEW.updated_at := NOW();  -- ✅

-- Use the TG_OP to detect the operation.
IF TG_OP = 'INSERT' THEN ...  -- ✅

-- Use the RAISE EXCEPTION to reject.
RAISE EXCEPTION 'Invalid' USING ERRCODE = '23514';  -- ✅

-- Use the INSTEAD OF for the view.
CREATE TRIGGER trg_insert INSTEAD OF INSERT ON view ...;  -- ✅

-- Disable the trigger during the bulk load.
ALTER TABLE orders DISABLE TRIGGER trg_audit;  -- ✅

-- Document the triggers.
COMMENT ON TRIGGER trg_audit ON orders IS 'The audit log';  -- ✅

❌ Don’t Do This:

-- Don't use the AFTER trigger to modify the row.
CREATE TRIGGER trg AFTER UPDATE ON orders ... NEW.total := 0;  -- ❌ The NEW is read-only

-- Don't use the INSTEAD OF on a table in PostgreSQL.
CREATE TRIGGER trg INSTEAD OF INSERT ON orders ...;  -- ❌ The view only

-- Don't forget the RETURN in the BEFORE trigger.
CREATE FUNCTION f() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at := NOW(); END; $$;  -- ❌ No RETURN

-- Don't create the trigger without the function.
CREATE TRIGGER trg BEFORE UPDATE ON orders ...;  -- ❌ The function is missing

-- Don't use the trigger for the application logic.
-- The trigger is the database rule.  -- ❌

-- Don't forget the FOR EACH ROW.
CREATE TRIGGER trg AFTER UPDATE ON orders ...;  -- ⚠️ The statement-level

Common Pitfalls

Pitfall	Why It Happens	Fix
The trigger does not fire	The wrong timing	Check the `BEFORE`/`AFTER`
The row not modified	No `RETURN NEW`	Add the return
The infinite recursion	The trigger updates the same table	Use the condition or the statement-level
The performance slow	The row-level trigger on the bulk	Disable during the load
The view not updatable	No `INSTEAD OF`	Add the trigger
The error not raised	No `RAISE`	Add the exception
The trigger disabled	The `DISABLE TRIGGER`	Enable it

Real-World Examples

1. The updated_at

CREATE TRIGGER trg_set_updated_at BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION set_updated_at();

2. The Validation

CREATE TRIGGER trg_validate BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION validate_order();

3. The Audit

CREATE TRIGGER trg_audit AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW EXECUTE FUNCTION audit_order_change();

4. The Cascade

CREATE TRIGGER trg_update_total AFTER INSERT OR UPDATE OR DELETE ON order_items FOR EACH ROW EXECUTE FUNCTION update_order_total();

5. The INSTEAD OF Insert

CREATE TRIGGER trg_insert INSTEAD OF INSERT ON user_profiles FOR EACH ROW EXECUTE FUNCTION insert_user_profile();

6. The SQL Server AFTER

CREATE TRIGGER trg_audit ON orders AFTER INSERT, UPDATE, DELETE AS ...

7. The MySQL BEFORE

CREATE TRIGGER trg_set BEFORE UPDATE ON orders FOR EACH ROW SET NEW.updated_at = NOW();

8. The Disable

ALTER TABLE orders DISABLE TRIGGER trg_audit;

9. The Drop

DROP TRIGGER trg_audit ON orders;

10. The Complete Pattern

CREATE TRIGGER trg BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION f();

Visual

The Trigger Timing

┌────────────────────────────────────────────────────────────┐
│  THE TRIGGER TIMING                                        │
│                                                            │
│  INSERT/UPDATE/DELETE                                      │
│    │                                                       │
│    ├── BEFORE trigger                                      │
│    │     → Validate the row                                │
│    │     → Modify the NEW                                  │
│    │     → Raise the exception                             │
│    │                                                       │
│    ├── The row is written                                  │
│    │                                                       │
│    └── AFTER trigger                                       │
│          → Audit the change                                │
│          → Cascade the update                              │
│          → The NEW is read-only                            │
│                                                            │
│  INSTEAD OF (view only)                                    │
│    → The default action is replaced                        │
│    → The trigger performs the underlying work              │
└────────────────────────────────────────────────────────────┘

The OLD and the NEW

┌────────────────────────────────────────────────────────────┐
│  THE OLD AND THE NEW                                       │
│                                                            │
│  INSERT                                                    │
│  ┌──────────────────────────────────────────────────┐     │
│  │  OLD: null                                         │     │
│  │  NEW: the new row                                  │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  UPDATE                                                    │
│  ┌──────────────────────────────────────────────────┐     │
│  │  OLD: the row before the change                    │     │
│  │  NEW: the row after the change                     │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  DELETE                                                    │
│  ┌──────────────────────────────────────────────────┐     │
│  │  OLD: the row being removed                        │     │
│  │  NEW: null                                         │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Audit Trigger

┌────────────────────────────────────────────────────────────┐
│  THE AUDIT TRIGGER                                         │
│                                                            │
│  UPDATE orders SET total = 100 WHERE id = 42;              │
│    │                                                       │
│    v                                                       │
│  AFTER trigger fires                                       │
│    │                                                       │
│    v                                                       │
│  INSERT INTO order_audit                                   │
│    (order_id, action, old_total, new_total, changed_at)    │
│  VALUES                                                    │
│    (42, 'UPDATE', 50, 100, NOW());                         │
│                                                            │
│  The audit row is the record of the change.                │
└────────────────────────────────────────────────────────────┘

The INSTEAD OF Trigger

┌────────────────────────────────────────────────────────────┐
│  THE INSTEAD OF TRIGGER                                    │
│                                                            │
│  INSERT INTO user_profiles (email, bio)                    │
│  VALUES ('alice@example.com', 'Hello');                    │
│    │                                                       │
│    v                                                       │
│  INSTEAD OF INSERT trigger fires                           │
│    │                                                       │
│    ├── INSERT INTO users (email) VALUES (...);             │
│    │     → The user_id is returned                         │
│    │                                                       │
│    └── INSERT INTO profiles (user_id, bio) VALUES (...);   │
│                                                            │
│  The view insert is split into the two table inserts.      │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
`BEFORE`	Runs before the row is written
`AFTER`	Runs after the row is written
`INSTEAD OF`	Replaces the operation on a view
`NEW`	The new row
`OLD`	The old row
`TG_OP`	The operation
`RAISE EXCEPTION`	The rejection
`FOR EACH ROW`	The row-level trigger
`FOR EACH STATEMENT`	The statement-level trigger
`DISABLE TRIGGER`	The temporary off

Key takeaways:

The BEFORE trigger runs before the row is written. It can read the OLD and the NEW, it can modify the NEW, and the modification is what the database stores. The validation, the updated_at, and the computed columns are the uses.
The AFTER trigger runs after the row is written. The change is visible, and the trigger cannot modify the row. The audit, the cascade, and the notification are the uses.
The INSTEAD OF trigger is only on a view. It intercepts the INSERT, the UPDATE, or the DELETE on the view and replaces the default action. The view that joins the tables is the classic case.
The NEW and the OLD are the row references. The NEW is the new row, and the OLD is the old row. The INSERT has the NEW only, the DELETE has the OLD only, and the UPDATE has both.
The TG_OP is the operation. The INSERT, the UPDATE, and the DELETE are the values. The trigger uses the TG_OP to branch the behavior.
The RAISE EXCEPTION rejects the change. The BEFORE trigger raises the exception, and the change is aborted. The error propagates to the application.
The FOR EACH ROW is the row-level. The trigger fires once per row. The FOR EACH STATEMENT fires once per statement, and the transition tables hold the rows.
The DISABLE TRIGGER is the bulk-load pattern. The trigger is disabled during the bulk insert, and the enable restores it. The pattern avoids the per-row overhead.

Remember: The database triggers are the automatic rules that run when the table changes. The BEFORE trigger validates or modifies the row, the AFTER trigger audits or cascades, and the INSTEAD OF trigger modifies the view. The NEW and the OLD are the row references, and the TG_OP is the operation. The RAISE EXCEPTION rejects the change, and the FOR EACH ROW is the row-level. The result is the database that enforces the rules, records the audit, and keeps the derived data in sync, regardless of the code path.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!

Why triggers exist

a. The BEFORE triggers

The PostgreSQL BEFORE trigger

The SQL Server INSTEAD OF and the AFTER

The MySQL BEFORE trigger

The validation trigger

The BEFORE trigger table

The OLD and the NEW

b. The AFTER triggers

The audit trigger

The TG_OP and the trigger context

The cascade trigger

The SQL Server AFTER trigger

The MySQL AFTER trigger

The AFTER trigger table

c. The INSTEAD OF triggers

The view-modification problem

The INSTEAD OF INSERT trigger

The INSTEAD OF UPDATE trigger

The INSTEAD OF DELETE trigger

The SQL Server INSTEAD OF trigger

The INSTEAD OF trigger table

Complete Example Session

Quick Reference

The Trigger Timing

The Trigger Events

The Trigger Variables

The Trigger Syntax

The Common Uses

Best Practices

Common Pitfalls

Real-World Examples

1. The updated_at

2. The Validation

3. The Audit

4. The Cascade

5. The INSTEAD OF Insert

6. The SQL Server AFTER

7. The MySQL BEFORE

8. The Disable

9. The Drop

10. The Complete Pattern

Visual

The Trigger Timing

The OLD and the NEW

The Audit Trigger

The INSTEAD OF Trigger

Summary

The `TG_OP` and the trigger context