28 June, 2011

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 !

.
.
.

5 comments:

Alex Nuijten said...

DDL statements always commits twice..

Randolf said...

Hi Hemant,

however it would be great if the developer was aware of the fact that a DDL in Oracle always does this implicit COMMIT no matter if there is a DDL trigger or not.

So the behaviour you've demonstrated can be seen without any custom DDL trigger. Just add a foreign key constraint to a pair of tables and try to truncate the table with the foreign key pointing to as part of a DML transaction: Although the truncate fails with an error due to the enabled FK constraint the DML will still be committed due to the implicit COMMIT.

Randolf

Hemant K Chitale said...

Alex, Randolf,
Yes, a DDL does an implicit COMMIT before and after it executes. I have referred to this behaviour in earlier blog posts / threads as well.

However, the point of this post was to show that even if a DDL were to be prevented from executing, the COMMIT before it still does get executed. As Randolf points out, this can happen in other cases as well, not just with DDL Triggers.

If you take the logic "Action A always includes an implicit action X ; if I prevent or fail Action A, then action X would also be prevented/failed", that logic does not apply if "action A" is a DDL statement.

Supposing that Action A is a DML statement and X is a Trigger on the table that the DML is operating on. If the DML fails, then the Trigger also rolls back.

However, if Action A is a DDL and you think that X is the pre-DDL commit, you'd be in for a surprise because the commit does get executed !


Hemant

Unknown said...

hemant,
I need to prevent one DROP against one trigger. h'ever i just checked web site and executed the trigger against TRUNCATE. but error is not coming.
i connected db as TEST01 and created this trigger. and created test table under TEST01.

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> create table x1(sal number);

Table created.

SQL> insert into x1 values(100);

1 row created.

SQL> commit;

Commit complete.

SQL> truncate table x1;

Table truncated.


Please guide me.

I originally wanted to achive this.

i have written one trigger ( ALTER_PWD_TRIGGER) and need to protect this trigger from CREATE/ALTER/DROP commands from other db users. if any user tried to perform CREATE/ALTER/DROP against this trigger ( other than trigger owner ), then i need to raiase an error message.
how to achive this ?

this is bit urgent. could you hlep me ?

Thank you
kesavan

Hemant K Chitale said...

Kesavan,

Check if you have the instance parameter "_system_trig_enabled" set to FALSE. That would disable firing of system triggers.


To protect a trigger you could use another trigger. But how would you protect the other trigger from a user who has admin privileges ? At some point, you have to trust the DBA.
Don't grant ADMINISTER DATABASE TRIGGER privilege to non-admin users.