A SQL trigger is the safer, more predictable choice when a database-side action must respond automatically to changes on a table. It is especially preferable to a PostgreSQL rule when side effects must be controlled. section 5.2
Choose a trigger when rule rewriting creates operational ambiguity:
- Rules rewrite the original query and can cause unintended side effects, such as incrementing a sequence more than once. section 5.2
- Using VOLATILE functions with rules is risky because those functions may execute multiple times. section 5.2
- Rules can alter an update with an added condition without preventing the original query from running, making their behavior difficult to reason about. section 5.2
- Prefer triggers when behavior must be well-understood and predictable rather than dependent on query rewriting. section 5.2 section 5.7
Auditing order insertions
An insertion into erp.order_groups was intended to create an audit.audit_log row containing the new order ID, current role, and timestamp. A rule-based implementation rewrote the insertion and could evaluate nextval() again, producing unintended sequence advancement and misleading audit timing. A trigger is the appropriate alternative when this audit side effect must occur predictably. section 5.2
