Working with Database Triggers
1. Creating Triggers
| Component | Detail |
|---|---|
| Event | INSERT / UPDATE / DELETE / TRUNCATE |
| Timing | BEFORE / AFTER / INSTEAD OF |
| Granularity | FOR EACH ROW / FOR EACH STATEMENT |
| Condition | WHEN (expr) |
| Body | Calls a trigger function (PG) or inline code |
Example: Audit Trigger (PG)
CREATE FUNCTION audit_orders() RETURNS TRIGGER LANGUAGE plpgsql AS $
BEGIN
INSERT INTO order_audit(order_id, op, changed_at, changed_by)
VALUES (COALESCE(NEW.id, OLD.id), TG_OP, NOW(), current_user);
RETURN COALESCE(NEW, OLD);
END;
$;
CREATE TRIGGER trg_audit_orders
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_orders();2. Understanding Trigger Types
| Type | Use |
|---|---|
| BEFORE row | Modify NEW before write, validation |
| AFTER row | Audit, propagation |
| BEFORE/AFTER statement | Set-level logic, batch counters |
| INSTEAD OF | Make views writable |
| DDL triggers | Schema-change auditing |
| Event triggers (PG) | Fire on DDL events |
3. Implementing Row-Level Triggers
| Variable | Meaning |
|---|---|
| NEW | New row (INSERT/UPDATE) |
| OLD | Old row (UPDATE/DELETE) |
| TG_OP | 'INSERT' / 'UPDATE' / 'DELETE' |
| TG_TABLE_NAME | Source table |
4. Implementing Statement-Level Triggers
| Aspect | Detail |
|---|---|
| Fire once per statement | Regardless of row count |
| Transition tables (PG/SQL Server) | REFERENCING NEW TABLE AS ... — set of affected rows |
| Use | Bulk counters, batch logging |
5. Using Trigger Events
| Event | Detail |
|---|---|
| INSERT | OLD is NULL |
| UPDATE [OF col] | Optional column filter |
| DELETE | NEW is NULL |
| TRUNCATE | Statement-level only |
6. Accessing Old and New Values
| Pattern | Use |
|---|---|
| NEW.col := ... | Mutate in BEFORE row trigger |
| IF NEW.col IS DISTINCT FROM OLD.col | Detect column changes |
| RAISE EXCEPTION | Block invalid changes |
| RETURN NULL (PG) | Skip operation (BEFORE row) |
7. Implementing Audit Trails with Triggers
| Approach | Detail |
|---|---|
| Audit table per source | Mirror columns + meta (op, ts, user) |
| Generic JSON audit | Store row_to_json(OLD/NEW) |
| Temporal tables (SQL Server, MariaDB) | Built-in history |
| CDC (Debezium) | External stream — avoids trigger overhead |
8. Cascading Trigger Execution
| Concern | Detail |
|---|---|
| Trigger on table A fires | Modifies table B → trigger on B fires |
| Recursion | Same trigger refires on its own writes — guard with column checks |
| Max depth (SQL Server) | 32 nested levels |
| Disable nesting | SQL Server: sp_configure 'nested triggers',0 |
9. Disabling and Enabling Triggers
| DB | Command |
|---|---|
| Postgres | ALTER TABLE t DISABLE TRIGGER name; / ENABLE |
| MySQL | Drop & recreate (no native disable) |
| SQL Server | DISABLE TRIGGER ALL ON t; |
| Oracle | ALTER TRIGGER name DISABLE; |
10. Managing Trigger Performance Impact
| Tip | Detail |
|---|---|
| Avoid heavy logic | Triggers add cost to every write |
| Prefer set-based | Use statement triggers with transition tables |
| No external calls | HTTP / FS calls block writes |
| Push to async | NOTIFY + worker, or CDC |
| Monitor | Track tx duration before/after deploy |