100+ PL/SQL Interview Questions and Answers for Experienced - 1

Question: 1

What are the events on which a database trigger can be based?

Database triggers are based on system events and can be defined at database or schema level. The various events on which a database trigger can be based are given as follows:

Data definition statement on a database or schema object

Logging off or on of a specific user

Database shutdown or startup

On any specific error that occurs

Question: 2

Which column of the USER_TRIGGERS data dictionary view displays the database event that will fire the trigger?

The Description column of the USER_TRIGGERS view combines information from many columns to display the trigger header, which includes the database event.

Question: 3

What is a SERVERERROR trigger?

A SERVERERROR trigger is executed when a server error occurs. This can be tracked using the built-in function of IS_SERVERERROR (error_number). This will return a Boolean value, which can be used to develop further logic.

Question: 4

What are the system privileges that are required by a schema owner (user) to create a trigger on a table?

A user must be able to alter a table to create a trigger on the table. The user must own the table and either have the ALTER TABLE privilege on that table or have the ALTER ANY TABLE system privilege. In addition, the user must have the CREATE TRIGGER system privilege. User should have the CREATE ANY TRIGGER system privilege to be able to create triggers in any other user account or schema. A database level event trigger can be created if the user has the ADMINISTER DATABASE TRIGGER system privilege.

Question: 5

Can a COMMIT statement be executed as part of a trigger?

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.

Related Questions