Triggers

Q1. Explain two virtual tables available at the time of database trigger execution.

Answer. :OLD and :NEW are the 2 virtual tables available at the time of database trigger execution. The table columns are referred as :OLD.column_name and :NEW.column_name.

Trigger Event	Availability of values in
	:NEW.column_name	:OLD.column_name
Insert	Available	Not Available
Update	Available	Available
Delete	Not Available	Available

Q2. How many types of database triggers can be specified on a table? What are they?

Answer.
There are 12 types of triggers, they are as follows.

Trigger type	Insert	Update	Delete
Before Row	o.k.	o.k.	o.k.
After Row	o.k.	o.k.	o.k.
Before Statement	o.k.	o.k.	o.k.
After Statement	o.k.	o.k.	o.k.

If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement. If WHEN clause is specified, the trigger fires according to the returned boolean value.

Q3. Can we create trigger on views? If yes, then which one?

Answer. Yes “Instead of” trigger can be created on views, which will update the data in the base table rows.

Q4. What are cascading triggers? What is the maximum no. of cascading triggers at a time?

Answer. When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Maximum no. of cascading triggers at a time is 32.

Q5. What is the execution order of database triggers?

Answer. The default execution order of database trigger is:

1. Statement level before triggers
2. Row level before triggers
3. Row level after triggers
4. Statement level after triggers

But if you have, say, two row level before trigger, by default you cannot be certain in which order those two are executed. But you can specify the order to cover even those cases. If you need to be certain about the order of trigger execution, you can specify this order when creating the trigger. This is done with the FOLLOWS … and PRECEEDS … options of the create trigger statement:

FOLLOWS | PRECEDES Specifies the relative firing of triggers that have the same timing point. It is especially useful when creating cross edition triggers, which must fire in a specific order to achieve their purpose.

Use FOLLOWS to indicate that the trigger being created must fire after the specified triggers. You can specify FOLLOWS for a conventional trigger or for a forward cross edition trigger.

Use PRECEDES to indicate that the trigger being created must fire before the specified triggers. You can specify PRECEDES only for a reverse cross edition trigger.

Q6. How to enable an disable the triggers?

Answer.
ALTER TRIGGER log_emp_update DISABLE;
ALTER TABLE departments DISABLE ALL TRIGGERS;
ALTER TRIGGER log_emp_update ENABLE;
ALTER TABLE departments ENABLE ALL TRIGGERS;

Q7. Table “Employee” exist in the database and has a synonym as “Emp”. Can we create triggers on “Emp”? If yes, then when we fire DML on EMPLOYEE, will the trigger created on EMP gets executed?

Answer. Yes, we can create the trigger on table Synonym & it will get fired too.

Q8. What are mutating triggers?

Answer. To mutate is to change. Mutating trigger error occurs when a row-level trigger tries to read or write the table from which the trigger was fired. This same restriction does not apply in statement-level triggers.

Q9. What happens if a procedure that updates a column of table X is called in a database trigger of the same table?

Answer. Mutation of table error occurs.

In case of any discrepancies or doubts do contact me.
Best of Luck Friends.