如何一次删除 Oracle 中给定数据库中的所有触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7981530/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to drop all triggers within given DB in Oracle at once?
提问by user1025759
How to drop (remove, delete) all triggers within given DB.
如何删除（移除、删除）给定数据库中的所有触发器。
Problem is that application requires DB upgrade and does not proceed triggers (suport, drop, create) after upgrade, application upgrade fails.
问题是应用程序需要数据库升级并且升级后不进行触发器（支持、删除、创建），应用程序升级失败。
回答by Bjarte Brandt
This will generate the command how to drop all triggers in current schema:
这将生成如何删除当前模式中的所有触发器的命令：
select 'drop trigger ' || trigger_name || ';' stmt from user_triggers;
回答by GTG
You can create a script for dropping triggers by using the Oracle system tables, like this:
您可以使用 Oracle 系统表创建用于删除触发器的脚本，如下所示：
select 'drop trigger ' || owner || '.' || trigger_name || ';' from all_triggers
Note that there are 3 views containing triggers:
请注意，有 3 个包含触发器的视图：
- all_triggers = all the triggers you have permission to know about (regardless of which schema they belong to)
- user_triggers = the triggers that belong to your own schema
- dba_triggers = for DBA's
- all_triggers = 您有权了解的所有触发器（无论它们属于哪个架构）
- user_triggers = 属于您自己架构的触发器
- dba_triggers = 用于 DBA
回答by saritonin
First Google hit for search query: Drop all triggers - Oracle
搜索查询的第一个 Google 命中：删除所有触发器 - Oracle
BEGIN
FOR i in (select trigger_name,owner
from dba_triggers
where trigger_name like '%_BI%' and owner = 'myTesting' ) LOOP
EXECUTE IMMEDIATE 'DROP TRIGGER '||i.owner||'.'||i.trigger_name;
END LOOP;
END;
If you really want to drop all the triggers in the database,
如果你真的想删除数据库中的所有触发器，
BEGIN
FOR i in (select trigger_name,owner
from dba_triggers ) LOOP
EXECUTE IMMEDIATE 'DROP TRIGGER '||i.owner||'.'||i.trigger_name;
END LOOP;
END;
