DDL Triggers

A few weeks ago, there was a question about disabling TRUNCATEs. That can be easily done via a Trigger.

But you have to be careful about such triggers. Such a trigger can disable a TRUNCATE and raise an error. It will *not*, however, prevent a COMMIT.

Here's an example of such a trigger :

SQL> -- create a trigger that raises an error on truncates
SQL> create or replace trigger prevent_truncates
 2  before truncate on schema
 3  begin
 4    raise_application_error(-20001,'TRUNCATE not permitted');
 5  end;
 6  /

Trigger created.

SQL>
SQL> -- test the trigger
SQL> create table TEST_TRIG_TBL_1 (primkey number primary key,  data_col varchar2(500));

Table created.

SQL>
SQL> truncate table TEST_TRIG_TBL_1;

truncate table TEST_TRIG_TBL_1
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TRUNCATE not permitted
ORA-06512: at line 2


SQL>  

So the trigger works as designed. It prevents a TRUNCATE in the schema.

But what if there IS data ?

SQL> -- what if there is data ?
SQL> create table TEST_TRIG_TBL_2 (primkey number primary key,  data_col varchar2(500));

Table created.

SQL> insert into TEST_TRIG_TBL_2 values (1,'ABCDEFGH');

1 row created.

SQL> insert into TEST_TRIG_TBL_2 values (2,'ZXCV');

1 row created.

SQL>
SQL> truncate table TEST_TRIG_TBL_2;
truncate table TEST_TRIG_TBL_2
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TRUNCATE not permitted
ORA-06512: at line 2


SQL>
SQL> -- is the data present ?
SQL> select * from TEST_TRIG_TBL_2 ;

  PRIMKEY
----------
DATA_COL
------------------------------------------------------------------------------------------------------------------------------------
        1
ABCDEFGH

        2
ZXCV


SQL>  

The data is present.

So, is there a COMMIT ?

SQL> -- do you realise what this means ?
SQL> -- suppose your transaction consists of more than 2 DML statements
SQL> -- one does an insert into table X and the other attempts to truncate table Y
SQL> -- if the truncate of Y fails, does the insert into X get rolled back ?
SQL>
SQL> create table X (primkey number primary key, data_col varchar2(5));

Table created.

SQL> create table Y (primkey number primary key, data_col varchar2(5));

Table created.

SQL>
SQL> -- insert into X
SQL> insert into X values (1,'QWERT');

1 row created.

SQL> insert into X values (2,'ASDF');

1 row created.

SQL>
SQL> -- truncate Y, the developer doesn't know that the TRUNCATE will fail
SQL> truncate table Y;
truncate table Y
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TRUNCATE not permitted
ORA-06512: at line 2


SQL>
SQL> -- oh ! there's an error. Let's ROLLBACK
SQL> rollback;

Rollback complete.

SQL>
SQL> -- is the data present in X ?
SQL> select * from X ;

  PRIMKEY DATA_
---------- -----
        1 QWERT
        2 ASDF

SQL>
SQL> -- HUH ?!  The TRUNCATE failed but the INSERT was committed !
SQL>  

Data that was INSERTed into X did not get rolled back although the TRUNCATE of Y failed. ! (Remember that the INSERT hadn't been committed so the transaction was still continuing and present till the TRUNCATE was issued).

Why does this happen ? Let's see a trace :

SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> truncate table Y;
truncate table Y
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TRUNCATE not permitted
ORA-06512: at line 2


SQL>  

The trace file actually shows these events in sequence :

XCTEND rlbk=0, rd_only=1, tim=1309191962248722
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 85567 and DROPSCN = 0
LOCK TABLE "Y" IN EXCLUSIVE MODE  NOWAIT
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 85567 and DROPSCN = 0
truncate table Y
begin
 raise_application_error(-20001,'TRUNCATE not permitted');
end;
declare
 m_stmt  varchar2(512);
begin
  m_stmt:='delete from sdo_geor_ddl__table$$';
  EXECUTE IMMEDIATE m_stmt;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
end;
.... and so on ... 

The first thing is the "XCTEND rlbk=0" which is a COMMIT. An Implicit COMMIT is issued by the TRUNCATE irrespective of the actions in the trigger.

For example, even if the trigger has an error it still causes a commit :

SQL> create or replace trigger prevent_truncates
 2  before truncate on schema
 3  begin
 4    raiseXXX_application_error(-20001,'TRUNCATE not permitted');
 5  end;
 6  /

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER PREVENT_TRUNCATES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3      PL/SQL: Statement ignored
2/3      PLS-00201: identifier 'RAISEXXX_APPLICATION_ERROR' must be
        declared

SQL> select status from user_triggers where trigger_name = 'PREVENT_TRUNCATES';

STATUS
--------
ENABLED

SQL>
SQL> create table Z (primkey number primary key, data_col varchar2(5));

Table created.

SQL> insert into Z values (1,'abc');

1 row created.

SQL> insert into Z values (2,'def');

1 row created.

SQL> insert into Z values (3,'ghi');

1 row created.

SQL> delete Z where primkey = 2 ;

1 row deleted.

SQL> truncate table Y;
truncate table Y
              *
ERROR at line 1:
ORA-04098: trigger 'HEMANT.PREVENT_TRUNCATES' is invalid and failed re-validation


SQL>
SQL> rollback;

Rollback complete.

SQL> select * from Z;

  PRIMKEY DATA_
---------- -----
        1 abc
        3 ghi

SQL>  

The single row delete in table Z has been committed even though the TRUNCATE trigger has failed to execute properly.


Remember : You as the DBA might put in a DDL trigger but a new developer (or even existing developer !) may not know about the presence of the trigger or the implications of the trigger !

.
.
.