RESTRICT DROP/TRUNCATE on TABLE

How to create Schema Level Trigger That Fires Before Drop Or Truncate On Table?

Sometimes, you accidentally Drop/Truncate some tables and then do lot of work to get the Table and data back to that table. To avoid such mistakes, it’s required to Protect such tables so you get an alert before actually performing such actions. 

This can be done by creating Schema Level Triggers and if in case it is really required to Drop/Truncate such objects- you can disable the Trigger and perform your Action.

For this we would need some Database Events which will fire such triggers and each Even will have some set of Attribute Functions. Here we need “BEFORE DROP” And “BEFORE TRUNCATE” Events and “ORA_DICT_OBJ_NAME” And “ORA_DICT_OBJ_TYPE” Attributes Functions.

Let’s create a Trigger for a Table you want to Restrict Drop and TRUNCATE.

Create or Replace Trigger RESTRICT_TABLE_TRG

Before Drop or Truncate

On Schema

WHEN (ORA_DICT_OBJ_NAME= 'TAB1' AND ORA_DICT_OBJ_TYPE= 'TABLE')

Begin

RAISE_APPLICATION_ERROR(-20001,'Protected : Action Restricted for this User');

End;

NOTE: TAB1 is the Table Name- You can change that as per your Requirement.

SQL> SELECT * From TAB1;

DATA
--------------------------------------------------
me@example.com
me@example
@example.com
me.me@example.com
me.me@ example.com
me.me@example-example.com

6 rows selected.

SQL> Create or Replace Trigger RESTRICT_TABLE_TRG
  2  Before Drop or Truncate
  3  On Schema
  4  WHEN (ORA_DICT_OBJ_NAME= 'TAB1' AND ORA_DICT_OBJ_TYPE= 'TABLE')
  5  Begin
  6  RAISE_APPLICATION_ERROR(-20001,'Protected : Action Restricted for this User');
  7  End;
  8  /

Trigger created.

/* Now, Trigger has been Created- Try To perform TRUNCATE/DROP on Table */

SQL> TRUNCATE TABLE TAB1;
TRUNCATE TABLE TAB1
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Protected : Action Restricted for this User
ORA-06512: at line 2


SQL> DROP TABLE TAB1;
DROP TABLE TAB1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Protected : Action Restricted for this User
ORA-06512: at line 2