Tuesday, May 13, 2014

AFTER DELETE Triggers do not Run After a Truncate

In this example, I want to show that Oracle does not execute an AFTER DELETE trigger after doing a TRUNCATE. I tested it on Oracle 11.2. First I created a table:

SQL> create table tab1 (my_name varchar2(10))
  2  /

Table created. 

SQL>

Then I inserted a row into it: 

SQL> insert into tab1 values ('Andrew')
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> select * from tab1
  2  /

MY_NAME
----------
Andrew

SQL>

Next I created a second table:

SQL> create table tab2 (my_name varchar2(10))
  2  /

Table created.

SQL>

I created a trigger to insert rows into TAB2 after deleting them from TAB1:

SQL> create or replace trigger trig1
  2  after delete on tab1
  3  for each row
  4  begin
  5  insert into tab2 (my_name) values (:old.my_name);
  6  end;
  7  /

Trigger created.


SQL>

To test the trigger, I ran a DELETE to remove the row from TAB1 and it appeared in TAB2:

SQL> delete tab1
  2  /

1 row deleted.

SQL> select * from tab1
  2  /

no rows selected

SQL> select * from tab2
  2  /

MY_NAME
----------
Andrew


SQL>

I did a ROLLBACK and the row returned from TAB2 to TAB1:

SQL> rollback
  2  /

Rollback complete.

SQL> select * from tab1
  2  /

MY_NAME
----------
Andrew

SQL> select * from tab2
  2  /

no rows selected


SQL>

I repeated the test with TRUNCATE:

SQL> truncate table tab1
  2  /

Table truncated.

SQL> select * from tab1
  2  /

no rows selected


SQL>

... but Oracle did not execute the trigger and the row did not appear in TAB2:

SQL> select * from tab2
  2  /

no rows selected

SQL>

... and after doing a ROLLBACK, the row did not return to TAB1 because TRUNCATE is DDL and includes a COMMIT:

SQL> rollback
  2  /

Rollback complete.

SQL> select * from tab1
  2  /

no rows selected

SQL> select * from tab2
  2  /

no rows selected

SQL>



No comments: