删除表后，依赖触发器会发生什么? [英] What happens to dependent triggers when the table is dropped?

问题描述

我有一张表backup，在该表上应用了一个触发器upd_trig.现在，我放下了表格，然后检查了是否所有关联的触发器/索引也都将被删除或保留在那里.

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

推荐答案

该表已删除，但它位于

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.

您可以清空回收站以永久删除其中的对象.要立即删除表而不将其移到回收站，可以将关键字purge添加到drop命令，如

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.

如果没有自动删除它，那么无论如何触发器都将是无关紧要的，因为您无法在删除的表上执行任何DML，因此它永远不会触发.那就是如果触发器 against 的表被删除了.您的触发器很奇怪，它正在插入同一张表中.通常，您会在备份表中的一张表上插入一个触发器(嗯，这是触发器的一种用法).在这种情况下，删除备份表将使活动表上的触发器无效，但不会删除该触发器.仅删除活动表会在活动表上删除触发器.

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.

这篇关于删除表后，依赖触发器会发生什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！