Triggers

Question 1

What is a trigger

Answer 1

Like a stored procedure can be invoked repeatedly.
Can enable and disable but can’t explicitly invoke it.
The DB automatically invokes it.

Fires when the ‘triggered’ event occurs.

Question 2

Compound Trigger

Answer 2

Can act before and after an UPDATE, INSERT or DELETE has occurred. Allows a trigger to process similar to a stored procedure without having to write such a procedure to call from a traditional trigger.

Can be used to avoid the mutating table error or to process and accept or reject updates to a table based upon desired criteria

Question 3

How are triggers classified

D D C I

Answer 3

DDL TRiggers

DML TRiggers

Compound Triggers

INSTEAD Of Triggers

Question 4

Trigger Execution Hierarchy

how is it followed when a trigger is fired

B N T F

Answer 4

BEFORE - statement level fires first

Next, BEFORE - row-level fires once for each affected row

Then, AFTER - row-level fires once for each affected row. This event alters between BEFORE and AFTER row level Triggers.

Finally, the AFTER statement-level trigger fires

Question 5

DDL Trigger

Answer 5

Associated with Data Definition Language (DDL)

Such as:
Dropping a Table
Altering a Column
Execute every time a DDL statement is executed

GENERALLY CREATED BY DBA for auditing and enforcement

Question 6

DML Trigger

And when is a DML Trigger fired

Answer 6

Data Manipulation Language
Fires at
row level each row is updated, insert or deleted
FOR EACH ROW Option

statement level - triggered for each SQL statement executed

Question 7

Compound Trigger and fires when…

Answer 7

Allows different blocks within a trigger to be executed at different timing points.
Declaration section and section for each timing point. Accessed from SQL statement

Question 8

Compound Trigger timing points

Answer 8

BEFORE The firing statement
BEFORE Each row that the firing statement affects
AFTER Each row that the firing statement affects
AFTER The firing statement

statement and row level can be put up in a single trigger

Question 9

INSTEAD of Trigger and when it fires

Answer 9

In the CREATE trigger is an alternative to BEFORE and AFTER options.

Transparent way to modify views that can’t be modified through Update, Insert and Delete statements

Fires the trigger instead of executing the trigger statement. Performs the operation on the underlying tables

Default activated for each row
View can’t be update, delete or insert if contains
Set operations, group functions, group by, connect by, start with or distinct or joins a subset of join views