Triggers

CREATE TRIGGER TriggerNameMode Event {OR Event }ON TargetTable [[REFERENCING ReferenceName] FOR EACH ROW [WHEN Predicate]] PL/SQL Block

• Before statements • For each tuple affected by the triggering statement: - Before row triggers are executed - Triggering statement is executed + integrity constraints checked on tuples - After row triggers • Integrity constraints on tables are checked • After triggers are checked

Same event triggers execution order is non-deterministic

Rollback of all operations performed by the trigger Rollback of the triggering transaction

Limit after which error is thrown: 32 triggers

Cannot be touched with ROW Triggers, but can be with statement triggers.

Condition can be specified only for row level triggers

CREATE TRIGGER TriggerName {BEFORE, AFTER} {INSERT, DELETE, UPDATE} ON TargetTable [REFERENCING ReferenceName] FOR EACH {ROW, STATEMENT} WHEN Predicate Procedural SQL Statements

Only one event (UPDATE, INSERT, DELETE) is allowed for each trigger.

Transaction T contains a statement S which generates event E. • T’s execution is suspended and its state is saved into a stack • Old and new values of E are computed • Before triggers on E are executed • New values are applied to the DB (modification due to E is actually performed) • constraints are checks to avoid recurive invocation on the same procedure. • After triggers due to E are executed. • If any trigger contains an action which triggers other triggers, those are executed in a recurive fashion. • The execution state of T is extracted from the stack and T is resumed.

Oracle: NO DB2: Yes

Oracle: no DB2: yes

Oracle: Specified DB2: Arbitrary

Oracle: Unspecified DB2: Creation order

Oracle: Yes DB2: No

Oracle: Yes, for row triggers DB2: No

An INSTEAD OF trigger is a trigger that allows you to skip an INSERT, DELETE, or UPDATE statement to a table or a view and execute other statements defined in the trigger instead.

Oracle: Yes DB2: No

Oracle: Yes DB2: Only for new variables