Trigger on DDL statement

This trigger gets fired when DDL statement such as CREATE, ALTER, or DROP command is issued. DDL triggers canbe associated with the database or with a schema. Moreover depending on the time of firing of trigger, this tigger canbe classified into BEFORE and AFTER. Hence the triggers van DDL statements can be as follows: – BEFORE CREATE and AFTER CREATE triggers fire when a schema object is created in the database or schema. – BEFORE ALTER and AFTER ALTER triggers fire when a schema object is altered in the database or schema. – BEFORE DROP and AFTER DROP triggers fire when a schema object is dropped from the database or schema. Example : Let us create a trigger called “no_drop_pass” that fires before dropping any object on the schema of the user with username “jyo”.It checks whether the object type and name. If the object name is ” passenger_det”and object type is table, it raises an application error and presents dropping of the table. The syntax for creating the trigger is as follows. SQL> connect Enter user-name :system Enter password :******* Connected. SQL>CREATE OR REPLACE TRIGGER no_drop_pass 2 BEFORE DROP ON JYO. SCHEMA 3 DECLARE 4 MESSAGE VARCHAR2 (1000) := 5 ‘No drop allowed on’ 6 DICTIONARY_ OBJECT_OWNER || ‘-‘|| 7 DICTIONARY_OBJECT_NAME ||’from’|| LOGIN_USER; 8 BEGIN 9 IF DICTIONARY_OBJ_OWNER = ‘JYO’ AND 10 DICTIONARY_OBJ_NAME = ‘PASSENGER_DET’ AND 11 DICTIONARY_OBJ_TYPE = ‘TABLE’ 12 THEN 13 RAISE_APPLICATION_ERROR (-20905, MESSAGE) ; 14 END IF; 15 END; 16 / Trigger created. The trigger is executed as shown below. SQL>connect Enter user-name: JYO Enter password : ***** Connected. SQL> drop table passenger_det; drop table passenger_det * ERROR at line 1: ORA-00604 : error occurred at recursive SQL level 1 ORA-20905 : No drop allowed on JYO. PASSENGER_DET from JYO ORA-06512 : at line 11