prevent objects getting dropped by user by mistake

[عبدالله أسعد]

This trigger secures the objects from getting dropped by mistake by a user.

Create a table for example called SECURED_OBJECTS with a column objectname with varchar2(20) o

Insert a record in this table with a value, the name of the object which should not get dropped

Create or replace trigger check_beforedrop before drop on database 
declare 
oname char(20); 
begin select objectname into oname from secured_objects where upper(objectname)=ora_dict_obj_name; 
if sql%found then 
RAISE_APPLICATION_ERROR(-20001,'You didnot want this object to be dropped'); 
end if; 
exception 
when no_data_found then 
dbms_output.put_line('This object will be dropped'); 
end;

Once this trigger is created, we can not the drop the object whose name is entered in the above table. In case it needs to be dropped ,remove the entry from the secured_objects table.

NOTE: This is a simple procedure to protect the objects from getting dropped.
Can be modified with more features.
Comments from DBA'S and develepers are requested

تم تعديل 15 نوفمبر 2004 بواسطة bedooracle