15+ PL/SQL Scenario Based Interview Questions for Experienced - 1

Question: 1

What is the difference between ON-SCHEMA and ON-DATA BASE triggers?

The ON-DATA BASE trigger is fired for all the schemas in the entire database; however, the ON-SCHEMA trigger is executed only for the schema in which the trigger is created, that is, the owner of the trigger.

Question: 2

What is the difference between ALTER TRIGGER and DROP TRIGGER statements?

An ALTER TRIGGER statement is used to recompile, disable, or enable a trigger; whereas, the DROP TRIGGER statement is used to remove the trigger from the database.

Question: 3

Can triggers reference other tables through a role?

Triggers may require referencing other tables while triggering an event. Therefore, privileges required to reference other tables must be granted directly to the owner of the trigger, as the trigger cannot reference other user tables through roles. For example, consider a trigger, which is used for any audit table. To insert a row in the audit table, the owner of the trigger must have the insert privilege on that audit table.

Question: 4

What are the different types of triggers?

There are following two types of triggers: Database triggers are executed implicitly whenever a Data Manipulation Language (DML) statement is carried out on a database table or a Data Definition Language (DDL) statement, such as CREATE or ALTER, is performed on the database. They may also be executed when a user or database event occurs, such as a user logs on or a database is shutdown. Application triggers are executed implicitly whenever a DML event takes place within an application, such as WHEN_NEW_FORM_INSTANCE in the Oracle Forms application.

Question: 5

It is possible to create STARTUP or SHUTDOWN trigger for ON-SCHEMA?

No, It is not possible to create STARTUP or SHUTDOWN triggers for ON-SCHEMA.