Harsh Chowdhary's Blog

In an organization there are instances when you cannot make any mistake of dropping an important table accidentally. You need to make sure that a trigger is defined for preventing such mistakes. While going through the Oracle document at Working With System Events you will find System events provide a mechanism for tracking system changes. There you get the list of Database Events. For each Event you get Attribute Functions. When a trigger is fired, you can retrieve certain attributes about the event that fired the trigger. Each attribute is retrieved by a function call. For our purpose, we need "Before Drop" and "Before Truncate" events and "ora_dict_obj_name" and "ora_dict_obj_type" Attribute Functions.

The following Trigger fires before a DROP or TRUNCATE statement, but not for a whole schema, only for one particular table in your schema:

Code:

SQL> connect scott/tiger
Connected.

SQL> create or replace trigger alter_dept
2 before drop or truncate
3 on schema
4 WHEN (ora_dict_obj_name = 'DEPT' AND ora_dict_obj_type = 'TABLE')
5 begin
6 RAISE_APPLICATION_ERROR(-20001,'Protected : Cannot alter table DEPT');
7 end;
8 /
Trigger created.

SQL> drop table dept;
drop table dept
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Protected : Cannot alter table DEPT
ORA-06512: at line 2


SQL> truncate table dept;
truncate table dept
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Protected : Cannot alter table DEPT
ORA-06512: at line 2

Client Used:- SQL Plus
Table:- Dept
Trigger:- alter_dept