Your audit trigger stopped logging deletions after a job switched from DELETE to TRUNCATE — why?

## What actually happened The original nightly job ran something like: ```sql DELETE FROM invoices WHERE archived = true; ``` and a trigger written as `AFTER DELETE ON invoices FOR EACH ROW` copied every removed row into `invoices_audit`. That trigger's contract is "run once per row deleted by a DELETE statement". Somebody replaced the job with `TRUNCATE TABLE invoices;` to make it faster. `TRUNCATE` does not delete rows individually — it removes the table's entire contents in one bulk operation — so there is no per-row deletion event for the trigger to fire on. The rows vanish, the audit table gains nothing, and no error is raised anywhere. That silence is the whole hazard. A failure that raised an error would have been caught the first night. A trigger that quietly does not apply produces a log that simply stops, and the gap is typically found weeks later by whoever needed the audit trail. ## Why the engine behaves this way Row-level triggers exist to observe and modify row transitions, and they need `OLD`/`NEW` row values to do their job. A bulk removal has no row-by-row transition to expose. Firing a per-row trigger for every row would also destroy the entire performance advantage of truncating — you would be paying `DELETE` costs with `TRUNCATE` syntax. So the engines skip them, uniformly: MySQL, SQL Server, Oracle and PostgreSQL all leave `DELETE` triggers unfired on a truncation. ## The statement-level escape hatch PostgreSQL provides a distinct trigger event for exactly this gap: ```sql CREATE TRIGGER audit_purge AFTER TRUNCATE ON invoices FOR EACH STATEMENT EXECUTE FUNCTION log_purge(); ``` It fires once per truncation, and it must be statement-level — there is no per-row variant, because there are no per-row values to hand it. That is enough to record *that* a purge happened, by whom and when; it is not enough to record *what* was purged, because the rows are gone and the trigger never saw them. If your audit requirement is "reconstruct the deleted data", a truncate trigger cannot satisfy it. Not every engine offers this event, so do not assume it is portable. ## The same blind spot, other mechanisms Once you have seen this, look for its siblings, because the pattern is "bulk statement bypasses per-row machinery": - **Change-data-capture and replication tooling** built on row-level change streams may represent a truncation very differently from a stream of deletes, or may need explicit support for it. - **Application-side listeners** that infer deletions from per-row events see nothing. - **Downstream row-count reconciliation** suddenly disagrees, with no corresponding delete events to explain the drop. The general lesson is that swapping `DELETE` for `TRUNCATE` is not a pure performance change. It changes the observable event stream the table emits, and anything subscribed to that stream is part of the blast radius. ## How to prevent it Three defences, in increasing strength. 1. **Document truncatability per table.** Tables carrying audit triggers, CDC subscriptions or referential children are `DELETE`-only; staging and derived tables may be truncated. Put it where migration authors will see it. 2. **Add the statement-level truncate trigger** where the engine supports it, so a purge at least leaves a footprint even if it cannot record row detail. 3. **Restrict the privilege.** Truncation usually requires a stronger right than `DELETE`. A service account granted `DELETE` but not truncate rights simply cannot make this mistake, which is a far more reliable control than a convention. ## Answering well Say the mechanism first — bulk removal means no per-row event, so row-level `DELETE` triggers never fire and nothing errors. Then show you know the boundary of the workaround: a statement-level truncate trigger records the event but not the data, and it is not available everywhere. Finish with the operational point: the choice between `DELETE` and `TRUNCATE` is a semantic decision about what the table is allowed to emit, not just a speed knob.