Triggers

Question 1

What are Triggers?

Answer 1

Triggers are stored programs, which are automatically executed or fired when some events occur

Question 2

What events can be used to execute a trigger?

Answer 2

• DML Statement (Delete, Update, Insert)
• DDL Statement (Create, Alter, Drop)
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)

Question 3

What is the syntax for creating a trigger?

Answer 3

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END;

Question 4

What are the 2 types of triggers?

Answer 4

• Statement-level trigger

- Row-level Trigger

Question 5

When does Statement-level triggers fire?

Answer 5

Fires once for a statement

Question 6

When does Row-level triggers fire?

Answer 6

Will fire for every row that’s effected by a statement

Question 7

What type of trigger uses :OLD and :NEW bind variables?

Answer 7

Row-level Trigger

Question 8

What type of DDL statement is :OLD meaningless in?

Answer 8

INSERT

Question 9

What type of DDL statement is :NEW meaningless in?

Answer 9

DELETE