Recycled Triggers.

A client complained that a number of triggers were in the users schema, with confusing names, which turned out to be from the recyclebin. Here’s how it’s done and the confusion it causes.

Here’s how the recyclebin works, for interest….
Using an empty schema, we have the following:
SQL> create table t1 as select * From user_tables where 1=0;

Table created.

SQL> create table t2 as select table_name from t1 where 1=0;

Table created.

create or replace trigger trg_t1
after insert on t1
referencing new as new old as old
for each row
begin
insert into t2 (table_name) values (:new.table_name);
end;
/

Trigger created.

And our objects.

SQL> select object_type, object_name from user_objects;

OBJECT_TYPE OBJECT_NAM
——————- ———-
TABLE T2
TABLE T1
TRIGGER TRG_T1

So, let’s insert a data row first.

SQL> insert into t1 select * from user_Tables where table_name = ‘T1′;

1 row created.

SQL> select * From t2;

TABLE_NAME
——————————
T1

So, what happens if we drop table t1 ?

SQL> Drop table t1;

Table dropped.

SQL> select object_type, object_name from user_objects;

OBJECT_TYPE OBJECT_NAM
——————- ———-
TABLE T2

SQL> flashback table t1 to before drop;

Flashback complete.

SQL> select object_type, object_name from user_objects;

OBJECT_TYPE OBJECT_NAME
——————- ——————————
TABLE T2
TABLE T1
TRIGGER BIN$b5vdBOSwSqeJ6Ac7EYeA/w==$0

Where’s our trigger ? It’s renamed. Once it was dropped, it was assigned a bin directory name, and this is retained when the table is restored.

Question now is, does it still work ?

SQL> select * From t2;

TABLE_NAME
——————————
T1

SQL> insert into t1 select * From user_tables where table_name = ‘T2′;

1 row created.

SQL> select * From t2;

TABLE_NAME
——————————
T1
T2

Yes, it does. Now, can we rename it ?

SQL> alter trigger “BIN$b5vdBOSwSqeJ6Ac7EYeA/w==$0″ rename to trg_t1;

Trigger altered.

SQL> select object_type, object_name from user_objects;

OBJECT_TYPE OBJECT_NAME
——————- ——————————
TABLE T2
TABLE T1
TRIGGER TRG_T1

So we can. So, why does it rename the trigger to the recyclebin object name ? It’s to stop an error on renaming I suspect. Check this.

SQL> drop table t1;

Table dropped.

SQL> select object_type, object_name from user_objects;

OBJECT_TYPE OBJECT_NAME
——————- ——————————
TABLE T2

SQL> create table t1 as select * From user_tables where table_name = ‘T2′;

Table created.

SQL> select table_name from t1;

TABLE_NAME
——————————
T2

SQL> flashback table t1 to before drop;
flashback table t1 to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object

Because we created a new T1 table. We can rename it if required, but let’s just create another trigger as before.

SQL> create or replace trigger trg_t1
2 after insert on t1
3 referencing new as new old as old
4 for each row
5 begin
6 insert into t2 (table_name) values (:new.table_name);
7 end;
8 /

Trigger created.

SQL> select object_type, object_name from user_objects;

OBJECT_TYPE OBJECT_NAME
——————- ——————————
TABLE T2
TRIGGER TRG_T1
TABLE T1

And what’s in the recyclebin ?

SQL> select * From user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME
—————————— ——————————– ——— ————————- ——————————
CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT
——————- ——————- ———- ——————————– — — ———- ———– ————
SPACE
———-
BIN$wdqbgQm6Q6+IM1UF7ajZkA==$0 TRG_T1 DROP TRIGGER
2010-01-22:13:30:24 2010-01-22:13:34:09 9.7232E+12 NO NO 199717 199715 199717

BIN$Sstqm0YURfGaO6/T1wfq7A==$0 T1 DROP TABLE USERS
2010-01-22:13:30:15 2010-01-22:13:34:09 9.7232E+12 YES YES 199715 199715 199715
8

Note the columns after the partition_name, it’s can_undrop and can_purge. The trigger can have no action on it, but the table can be purged or restored. This is because the trigger belongs to the table and will be restored with it.

So we cannot restore t1 to before drop unless we rename it.

SQL> flashback table t1 to before drop rename to t0;

Flashback complete.

SQL> select object_type, object_name from user_objects;

OBJECT_TYPE OBJECT_NAME
——————- ——————————
TABLE T2
TABLE T0
TRIGGER BIN$wdqbgQm6Q6+IM1UF7ajZkA==$0
TRIGGER TRG_T1
TABLE T1

There’s the recycle bin name which has the same name as was allocated to it in the recyclebin.

SQL> select * From user_recyclebin;

no rows selected

Why does oracle do this ? Imagine a table with several triggers on it, which you drop. You then recreate the table and want to restore the old one, which you can do with the flashback command. However, with the triggers on the table, then you would have to restore and rename the table, and all the triggers.

Perhaps it would be simpler if oracle could check for the existing trigger name, and append something to it to keep the naming relevant, even if only a sequence number. What I would like to see is the table restored, and the trigger TRG_T1 renamed to TRG_T1_R1 or something.

Check the recyclebin example here for more information.

This entry was posted in Oracle and tagged , , , . Bookmark the permalink.