4

I have one table backup on which I had applied one trigger upd_trig. Now, I dropped my table and then I checked, whether all the associated trigger/index will also been dropped or will remain there.

As I found some discussion here,and they said Trigger/Index all will be dropped,once we drop our table. But, it seems, trigger still exist. Can anyone explain, what exactly happens, when we drop the table 

SQL> drop table backup;

Table dropped.

SQL> select text from user_source;

TEXT
----------------------------------------------------------------------------------------------------
TRIGGER
"BIN$Dg5j/bf6Rq6ugyN5ELwQkw==$0" BEFORE UPDATE ON backup FOR EACH ROW
BEGIN
INSERT INTO BACKUP VALUES(USER,:OLD.ENAME,SYSDATE);
END;

9 rows selected.

SQL> select count(*) from user_triggers;

  COUNT(*)
----------
         1

SQL> select trigger_name from user_triggers;

TRIGGER_NAME
------------------------------
BIN$Dg5j/bf6Rq6ugyN5ELwQkw==$0
Ben
  • 51,770
  • 36
  • 127
  • 149
Ravi
  • 30,829
  • 42
  • 119
  • 173

1 Answers1

8

The table has been dropped, but it is in the recycle bin, from which it can be recovered using the flashback commands (flashback ... before drop]. The name showing as BIN$... is a bit of a giveaway. The trigger is also showing with a BIN$... name, indicating that it is in the recycle bin too, and any indexes will be too.

You can empty the recycle bin to permenantly remove the objects in it. To drop a table immediately, without it going to the recycle bin, you can add the keyword purge to the drop command, as explained in the documentation. That will also drop any indexes and triggers immediately.

If it wasn't dropped automatically, then the trigger would be irrelevent anyway, since you couldn't perform any DML on the dropped table, so it could never fire. That's if the table the trigger is against is dropped. Your trigger is weird, it's inserting into the same table. Normally you'd have a trigger on one table insert into your backup table (well, for one use of triggers). In that case, dropping the backup table would invalidate the trigger on the live table, but would not drop it. Only dropping the live table would drop the trigger on the live table.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • but my question remain unanswered, whether it drop my trigger/index, which are associated with my table. ? – Ravi Apr 02 '13 at 18:31
  • @jWeavers - it does drop, but also puts those in the recycle bin if it's configured. Updated the answer.... is that clearer? – Alex Poole Apr 02 '13 at 18:36
  • ok got it.. means trigger is not available as it was. The trigger,which i had applied on my table is been dropped with my table and currently it is showing from recyclebin of oracle. – Ravi Apr 02 '13 at 18:40