DROP and TRUNCATE Restriction in Oracle database.

SQL> CREATE TABLE OBJECT_EVENT_ALLOWED
  2  (OBJECT_NAME VARCHAR2(50),
  3   DROP_ALLOWED CHAR(1),
  4   TRUNCATE_ALLOWED CHAR(1),
  5   DELETE_ALLOWED CHAR(1)
  6   );
Table created.
SQL> CREATE TABLE ORA_EVENT_LOG
  2  (
  3    OBJECT_NAME     VARCHAR2(100 BYTE),
  4    EVENT      VARCHAR2(50 BYTE),
  5    OWNER      VARCHAR2(50 BYTE),
  6    MACHINE    VARCHAR2(100 BYTE) DEFAULT SYS_CONTEXT ('USERENV', 'HOST'),
  7    IPADRES    VARCHAR2(100 BYTE)  DEFAULT SYS_CONTEXT('USERENV','IP_ADDRESS'),
  8    TIMESTAMP  DATE DEFAULT SYSDATE
  9  );
Table created.
SQL> CREATE TABLE TEST_TRUNCATE
  2  (
  3    DATA1  VARCHAR2(20 BYTE)
  4  );
Table created.
SQL> INSERT INTO OBJECT_EVENT_ALLOWED
  2     (OBJECT_NAME, DROP_ALLOWED, TRUNCATE_ALLOWED, DELETE_ALLOWED)
  3   VALUES
  4     ('TEST_TRUNCATE', 'Y', 'Y', 'Y');
1 row created.
SQL> COMMIT;
Commit complete.

SQL> CREATE OR REPLACE TRIGGER DTR_EVENT_CHECK_STORE
  2     BEFORE DROP OR TRUNCATE ON SCHEMA
  3  DECLARE
  4     V_OBJNAME   VARCHAR2 (50);
  5     V_OBJECT_TYPE VARCHAR2(30);
  6     V_EVENT     VARCHAR2 (30);
  7     V_OBJECT_COUNT NUMBER(5);
  8  BEGIN
  9     SELECT ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, ORA_SYSEVENT
 10       INTO V_OBJNAME, V_OBJECT_TYPE, V_EVENT
 11       FROM DUAL;
 12
 13       IF V_EVENT IN ('DROP','TRUNCATE') AND V_OBJECT_TYPE IN ('TABLE') THEN
 14          IF V_EVENT='DROP' THEN
 15              BEGIN
 16                  SELECT COUNT(OBJECT_NAME)
 17                  INTO V_OBJECT_COUNT
 18                  FROM OBJECT_EVENT_ALLOWED
 19                  WHERE UPPER(OBJECT_NAME)=UPPER(V_OBJNAME)
 20                  AND DROP_ALLOWED='Y';
 21              END;
 22          END IF;
 23
 24          IF V_EVENT='TRUNCATE' THEN
 25              BEGIN
 26                  SELECT COUNT(OBJECT_NAME)
 27                  INTO V_OBJECT_COUNT
 28                  FROM OBJECT_EVENT_ALLOWED
 29                  WHERE UPPER(OBJECT_NAME)=UPPER(V_OBJNAME)
 30                  AND TRUNCATE_ALLOWED='Y';
 31              END;
 32          END IF;
 33
 34           IF V_OBJECT_COUNT=0 THEN
 35                  RAISE_APPLICATION_ERROR(-20099, V_EVENT||' Not allowed in '||V_OBJNAME|| ' '||V_OBJECT_TYPE);
 36             ELSE
 37                  BEGIN
 38                      INSERT INTO ORA_EVENT_LOG( OBJECT_NAME, EVENT, OWNER) VALUES (V_OBJNAME, ORA_SYSEVENT, USER);
 39                    EXCEPTION
 40                          WHEN OTHERS THEN
 41                          NULL;
 42                  END;
 43           END IF;
 44
 45       END IF;
 46
 47  END DTR_EVENT_CHECK_STORE;
 48  /
Trigger created.
SQL> INSERT INTO OBJECT_EVENT_ALLOWED
  2     (OBJECT_NAME, DROP_ALLOWED, TRUNCATE_ALLOWED, DELETE_ALLOWED)
  3   VALUES
  4     ('TEST_TRUNCATE', 'Y', 'Y', 'Y');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> TRUNCATE TABLE TEST_TRUNCATE;
Table truncated.
SQL> DELETE FROM OBJECT_EVENT_ALLOWED;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> TRUNCATE TABLE TEST_TRUNCATE;
TRUNCATE TABLE TEST_TRUNCATE
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20099: TRUNCATE Not allowed in TEST_TRUNCATE TABLE
ORA-06512: at line 33

SQL>