Lecture 11

Question 1

What is a trigger? How does it work?

Answer 1

A stored PL/SQL subprogram associated with a table, it is automatically invoked by the DBMS in response to database changes.
Triggers follow the Event-Condition-Action model:
Event: caused by statments, such as INSERT, UPDATE, or DELETE. Trigger will be fired -BEFORE or AFTER the triggering statement is executed, depending on setup.
A triggering condition is optional, but determines whether the action should go ahead, finally, the action step is a block of PL/SQL statements to be executed.

Question 2

How do we define a trigger?

Answer 2

CREATE [OR REPLACE] TRIGGER name {BEFORE | AFTER | INSTEAD OF} {DELETE | INSERT |UPDATE} etc.

Question 3

What are the two main types of triggers?

Answer 3

Row-level triggers, these execute once for each row affected by the triggering event, and statement level triggers, which only execute once no matter how many rows were affected.

Question 4

What are OLD. and NEW. in relation to triggers?

Answer 4

OLD. is the value of the attribute before a change from UPDATE or DELETE, will be NULL for INSERTS.
NEW. the value of the attribute after an UPDATE or INSERT, NULL for DELETE statements. NEW AS newname or OLD AS oldname to create aliases.
Must be preceded by : in trigger body, but not in when clause.

Question 5

How can we tell what activity caused the trigger to execute?

Answer 5

Trigger attributes, the trigger has the INSERTING, UPDATING and DELETING attributes, one will be set to true.

Question 6

What is Oracle’s execution model?

Answer 6

All before statement triggers that apply to the SQL statement -> loop for each row(BEFORE row triggers-> change row -> AFTER row triggers) -> complete integrity constraint checks -> AFTER statement triggers.

Question 7

What is a mutating table? When can a mutating error occur?

Answer 7

A mutating table is one that is currently being modified by an UPDATE, DELETE, or INSERT, or a table that might need to be updated by a DELETE CASCADE referential integrity constraint.
Mutating errors occur when the SQL statements of a trigger read from or modify a mutating table of the triggering statement.

Question 8

What is a constraining table? What are the restrictions?

Answer 8

A table a triggering statement might need to read, directly or indirectly(referential integrity constraint).
The statements of a trigger cannot change the PRIMARY, FOREIGN, or UNIQUE KEY columns of a constraining table of the triggering.