Question For Trigger

PL/SQL interview question for Trigger

1. What is Trigger? how many type of trigger ?
2. Why we will use trigger?
3. Mutating in Trigger.
4. Can we write trigger over view? Write one program to update view.
5. What is the use of INSTEAD OF in trigger?
6. What is Compound trigger and what is the use of compound Trigger?
7. What is the use of FOLLOWS clause or how to declare the order of trigger?
8. Normal trigger execution order .
9. Difference between ROW level and Statement level trigger?
10. Difference between BEFORE and AFTER trigger?
11. How can we improve the performance of a trigger ?
12. What is a CALL statement? Explain with an example.What is a CALL statement? Explain with an example.
13. What is the difference between database trigger and stored procedure?
14. COMMIT statement can be executed as part of a trigger?



Ans

1. A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database. The triggers executes implicitly whenever a particular event takes place.

Type OF Trigger:

i. Application Trigger:

 Application triggers execute implicitly whenever a particular data manipulation language (DML) event occurs within an application (ex. Oracle Forms, Oracle Reports)

ii. Database trigger: 

These triggers are fired whenever a data event (such as DML,DDL) or system event (such as logon or shutdown) occurs on a schema or database.

2. Advantages Of Database Trigger:

Improve Data Security:

• Provide enhanced and complex security checks
• Provide enhanced and complex auditing

Improve Data integrity:

• Enforce dynamic data integrity constraints
• Enforce complex referential integrity constraints
• Ensure that related operations are performed together implicitly

3. Mutating in Trigger:

4. Yes, we can write trigger over view.

5.

11. The performance of a trigger can be improved by using column names along with the UPDATE clause in the trigger. This will make the trigger fire when that particular column is updated and therefore, prevents unnecessary action of trigger when other columns are being updated.

12. A CALL statement within a trigger enables you to call a stored procedure within the trigger rather than writing the Procedural Language/Structured Query Language (PL/SQL) code in it, The procedure may be in PL/SQL, C, or Java language. 

Following is an example of the CALL statement:

CREATE OR REPLACE TRIGGER [trigger_name]

BEFORE UPDATE OF [column_name]  ON [table_name]
FOR EACH ROW
WHEN [condition_clause]
CALL [procedure_name]

13. 

• The main difference between database trigger and stored procedure is that the trigger is invoked implicitly and stored procedure is invoked explicitly.
• Transaction Control statements, such as COMMIT, ROLLBACK, and SAVEPOINT, are not allowed within the body of a trigger whereas, these statements can be included in a stored procedure.

14. No, A COMMIT statement cannot be executed as a part of a trigger because it is a Transaction Control statement, which cannot be executed within a trigger body. Triggers fire within transactions and cannot include any Transaction Control statement within its code.