Which statement is true about triggers on data definition language (DDL) statements?
A.
They can be used to track changes only to a table or index.
B.
They can be defined by all users in the database or only by a specific user.
C.
They are fired only when the owner of the object Issues the DDL statement.
D.
They can be used to track changes to a table, table space, view, or synonym.
b, d
a – incorrect: for example view can be dropped
c – incorrect: it depentds on trigger level (database or schema)
Only D is correct
It seems that question was changed or i was inattentive…
b is incorrect too, to create trigger user needs at least “create trigger privilige”
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS2003
D
b,d
If I have to pick only one I will pick D. B is only correct if you have the privileges to do so as Uladzimir well mentioned.
DDL triggers: Using DDL triggers, DBA can automatically track all changes to the database, including changes to tables, indexes, and constraints.
http://www.dba-oracle.com/t_ddl_triggers.htm
b is incorrect too, to create trigger user needs at least “create trigger privilige”.
To create a trigger in your own schema on a table in your own schema or on your own schema, you must have the CREATE TRIGGER system privilege.
B,D
Only have to choose one answer which is D.
@sys as sysdba >revoke create any trigger from scott;
@Scott >
create or replace trigger drop_trg
before drop on green.schema
begin
raise_application_error(-20000,’Do not Drop any Object’);
end;
TRIGGER DROP_TRG compiled
@green> create table dummy(c1 number);
drop table dummy;
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Do not Drop any Object
That means DDL Triggers can be created by any user on another schema without “Create Any Trigger” privilege also
D. –>wrong as even create function/proc DDL EVENTs can be tracked
Only answer C is correct.
“DDL triggers are created as system triggers and therefore must be created either at
the schema or database level. When created at the schema level, the trigger will fire
whenever the user that owns the trigger initiates the triggering event. If any other
user performs that event, the trigger will not fire. By contrast, a database-level
trigger will fire whenever any user initiates the event.”
So, if the DDL trigger is a database level trigger, C will be incorrect.
Here is an example (using schema sys)
—————–
create or replace TRIGGER tr_audit
BEFORE ALTER ON DATABASE
BEGIN
insert into hr.t_audit values (sysdate, ora_dict_obj_owner || ‘.’ || ora_dict_obj_name, ora_dict_obj_type);
END;
D