SQL Trigger リファレンス
リファレンス 用 SQL triggers: BEFORE/AFTER, INSERT/UPDATE/DELETE, および statement vs 行 triggers.
Trigger Types
BEFORE triggers run before the operation, can modify NEW values, and cancel the operation. AFTER triggers run after, cannot modify the row, and are used for auditing/cascading. INSTEAD OF triggers (views) replace the operation. Row triggers fire per affected row; statement triggers fire once per statement. UPDATE triggers can specify columns (OF column list). Triggers can be recursive (trigger causes another trigger). Trigger order when multiple exist varies by database. Use triggers sparingly as they add hidden complexity.
- BEFORE
- Can modify NEW
- AFTER
- Post-operation
- row vs stmt
- Per-row vs per-statement
cancel operationaudit, cascadegranularityUse Cases and Caveats
Common uses: audit logging, derived column maintenance, complex validation, cascading updates. Triggers are hidden from application code, making debugging harder. They run in the same transaction as the triggering statement. Mutating table errors occur when triggers query the triggering table (Oracle). Performance impact: triggers add overhead per row. Alternatives: application-level logic, stored procedures, or scheduled jobs. When using triggers, document them clearly and prefer declarative constraints where possible. Test trigger behavior thoroughly.
- audit
- Log changes
- hidden
- Not visible in app
- alternatives
- App logic, constraints
AFTER triggerdebugging issueprefer declarative