Ever deleted some tables accidently and had to waste your time doing them all over again? Oracle, in conjunciton with Triggers, provides an easy solution to this problem.
First, create a table that holds the table name. Say you create a table called SafeTable with a column names SafeTableName and the datatype varchar2. Populate this table with the table names that should not be dropped. Then go ahead and create the trigger as follows:
CREATE or REPLACE TRIGGER checkForSafeTable BEFORE DROP ON DATABASEDECLARE TABLENAME CHAR(20);BEGIN SELECT SafeTableName INOT TABLENAME from SafeTable WHERE UPPER(TABLENAME)=ora_dict_obj_name; IF sql%found THEN RAISE_APPLICATION_ERROR(-20001,'This is a SAFE table'); END IF;EXCEPTION WHEN no_data_found THEN dbms_output.put_line('This table will be dropped');END;
When you create this trigger, you cannot drop any table that has an entry in the SafeTable.