Triggers

Question 1

Oracle Trigger Syntax

Answer 1

CREATE TRIGGER

TriggerNameMode Event {OR Event }ON TargetTable

[[REFERENCING ReferenceName]

FOR EACH ROW

[WHEN Predicate]]

PL/SQL Block

Question 2

Oracle Execution Algorithm

Answer 2

1. Before statements
2. 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
3. Integrity constraints on tables are checked
4. After triggers are checked

Question 3

Oracle Trigger Execution Order

Answer 3

Same event triggers execution order is non-deterministic

Question 4

Oracle: When Error Occurs

Answer 4

Rollback of all operations performed by the trigger

Rollback of the triggering transaction

Question 5

Oracle: Cascade Trigger Execution

Answer 5

Limit after which error is thrown: 32 triggers

Question 6

Oracle: Mutating Table Access

Answer 6

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

Question 7

Oracle: Conditional Execution

Answer 7

Condition can be specified only for row level triggers

Question 8

DB2 Syntax

Answer 8

CREATE TRIGGER TriggerName

{BEFORE, AFTER} {INSERT, DELETE, UPDATE}

ON TargetTable

[REFERENCING ReferenceName]

FOR EACH {ROW, STATEMENT}

WHEN Predicate

Procedural SQL Statements

Question 9

DB2 Limitations

Answer 9

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

Question 10

DB2 Execution algorithm

Answer 10

Transaction T contains a statement S which generates event E.

1. T’s execution is suspended and its state is saved into a stack
2. Old and new values of E are computed
3. Before triggers on E are executed
4. 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.
5. After triggers due to E are executed.
   
   • If any trigger contains an action which triggers other triggers, those are executed in a recurive fashion.
6. The execution state of T is extracted from the stack and T is resumed.

Question 11

Oracle VS. DB2

Reference to Old_Table and New_table in statement triggers

Answer 11

Oracle: NO

DB2: Yes

Question 12

Oracle VS. DB2

When clause in statement triggers

Answer 12

Oracle: no

DB2: yes

Question 13

Oracle VS. DB2

Execution order between row and statement triggers with same mode

Answer 13

Oracle: Specified

DB2: Arbitrary

Question 14

Oracle VS. DB2

Execution order between triggers with same event, mode, granularity

Answer 14

Oracle: Unspecified

DB2: Creation order

Question 15

Oracle VS. DB2

More than one triggering event allowed

Answer 15

Oracle: Yes

DB2: No

Question 16

Oracle VS. DB2

Forbidden access to the mutating table

Answer 16

Oracle: Yes, for row triggers

DB2: No

Question 17

Oracle’s INSTEAD OF

Answer 17

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.

Question 18

Oracle VS. DB2

Availability of the INSTEAD OF sematincs

Answer 18

Oracle: Yes

DB2: No

Question 19

Oracle VS. DB2

Database modifications allowed in before triggers

Answer 19

Oracle: Yes

DB2: Only for new variables