Db2 triggers: FOR EACH ROW, FOR EACH STATEMENT, DB2ROW, DB2SQL

There is some confusion about the FOR EACH, the MODE, and the BEFORE / AFTER clauses of Db2 CREATE TRIGGER. While other clauses are common to most DBMSs, MODE is a Db2 unique feature. Their meaning is simpler than it seems.

Before discussing the clauses, keep in mind that INSERT, UPDATE and DELETE can write any number of rows, from zero to the number of rows in a table:

DELETE FROM employee WHERE department = 'MARKETING';
  • FOR EACH:
    • FOR EACH STATEMENT A trigger is only executed once per statement. It is executed even if the statement did not write any row. Use it, for example, to log that a statement is run.
    • FOR EACH ROW: The trigger is executed once for each written row: zero or more times. Use it, for example, to log or validate each row change.
  • Timing:
    • BEFORE: The trigger is executed before modifying a row. Only allowed with FOR EACH ROW.
    • AFTER: The trigger is run after modifying a row (FOR EACH ROW) or running a statement (FOR EACH STATEMENT).

The MODE clause is only used with FOR EACH ROW triggers, and it further specifies when the trigger must run.

  • MODE DB2SQL is only valid for AFTER, FOR EACH ROW triggers, and it means that the trigger is run after all the changes are written.
  • MODE DB2ROW means that the trigger is run BEFORE or AFTER each row change.

Remember this rule of thumb: If the trigger specifies an action that will happen for each row, MODE specifies whether the trigger should be run at the end of the query.

Reference

Db2 documentation: