Ask TEM Dba

This trigger secures objects, preventing a user from dropping them by mistake. To begin, create a table for the example below called secured_objects with a column objectname, with datatype varchar2(20). Insert a record into this table with a value, the name of the object, which should not get dropped. Then, create the following trigger:

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 did not want this object to be dropped');
  end if;
exception
  when no_data_found
  then dbms_output.put_line('This object will be dropped');
end;

After this trigger is created, you cannot drop an object whose name is entered in the table. In the case where you need to drop an object, remove the entry from the secured_objects table.