Oracle Database 11g: Program with PL/SQL — Question 18

User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA. HR is an existing schema in the database.
SCOTT creates the following trigger:
CREATE OR REPLACE TRIGGER drop_trigger

BEFORE DROP ON hr.SCHEMA -

BEGIN -
RAISE_APPLICATION_ERROR (-20000, Cannot drop object');
END:
SCOTT does not grant the execute privilege on this trigger to any other users.
For which user(s) would this trigger fire by default when they drop an object in the hr schema?

Answer options

• A. Only HR
• B. SCOTT and HR
• C. Only SCOTT
• D. SCOTT, HR, and SYS

Correct answer: A

Explanation

The trigger will fire only for the HR user when they attempt to drop an object in the HR schema because the trigger is defined as a BEFORE DROP trigger on the HR schema. Since SCOTT has not granted execute privileges to any other users, only HR will activate the trigger when performing a drop operation.