There is some confusion about the
FOR EACH, the
MODE, and the
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
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 STATEMENTA 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.
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).
MODE clause is only used with
FOR EACH ROW triggers, and it further specifies when the trigger must run.
MODE DB2SQLis only valid for
FOR EACH ROWtriggers, and it means that the trigger is run after all the changes are written.
MODE DB2ROWmeans that the trigger is run
AFTEReach 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.