Using Trigger restrict to drop table

Using BEFORE DROP Trigger you will restrict to drop database objects.

When a user connected as XX tries to drop a database object, the database fires the trigger before dropping the object.

Example:

    

       1) Create table:

       

          Create Table xx.xx_test_007

               (emp_no NUMBER,

                emp_name VARCHAR2(200),

                doj DATE

               );

       Output: table XX.XX_TEST_007 created.

      2) Create Trigger   

          Schema Name:  XX

     

         CREATE OR REPLACE TRIGGER drop_trigger

         BEFORE DROP ON XX.SCHEMA

         BEGIN

            RAISE_APPLICATION_ERROR (

               num => -20000,

               msg => 'Cannot drop object');

         END;

        /

   

      Output: TRIGGER DROP_TRIGGER compiled

      3) Try to drop table PO.XX_TEST_007

   

        DROP TABLE XX.XX_TEST_007;

      Output:

               SQL Error: ORA-00604: error occurred at recursive SQL level 1

               ORA-20000: Cannot drop object

               ORA-06512: at line 2

               00604. 00000 -  "error occurred at recursive SQL level %s"

               *Cause:    An error occurred while processing a recursive SQL statement

                          (a statement applying to internal dictionary tables).

               *Action:   If the situation described in the next error on the stack

                          can be corrected, do so; otherwise contact Oracle Support.