如何禁用所有与 Oracle 中的表相关的触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5514634/
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 disable all triggers that concerns a table in Oracle?
提问by alci
In Postgresql, if I do ALTER TABLE mytable DISBLE TRIGGERS ALL, all triggers and constraints regarding this table are suspended.
在 Postgresql 中，如果我这样做ALTER TABLE mytable DISBLE TRIGGERS ALL，有关该表的所有触发器和约束都将暂停。
Especially, foreign keys from other tables to mytable are suspended, and I can delete from mytable without problem. I have the risk to break database consistency, but I know what I'm doing, and I must have superuser privileges.
特别是，从其他表到 mytable 的外键被挂起，我可以毫无问题地从 mytable 中删除。我有破坏数据库一致性的风险，但我知道我在做什么，而且我必须拥有超级用户权限。
How do I do the same in Oracle ? I am under the impression that ALTER TABLE mytable DISBLE ALL TRIGGERSin Oracle will suspend all trigger and constraints belonging to mytable, but not those that concerns mytable but belong to other tables (especially foreign keys).
我如何在 Oracle 中做同样的事情？我的印象是ALTER TABLE mytable DISBLE ALL TRIGGERS在 Oracle 中将挂起所有属于 mytable 的触发器和约束，但不会挂起与 mytable 相关但属于其他表（尤其是外键）的触发器和约束。
Am I right and what would be the way to achieve the same result as in Postgresql in Oracle ?
我是对的，在 Oracle 中实现与 Postgresql 相同的结果的方法是什么？
回答by APC
The syntax does disable triggers in Oracle:
语法在 Oracle 中禁用触发器：
SQL> select trigger_name, status from user_triggers
2 where table_name='TEST'
3 /
TRIGGER_NAME STATUS
------------------------------ --------
TEST_TRIGGER ENABLED
SQL> ALTER TABLE test DISABLE ALL TRIGGERS
2 /
Table altered.
SQL> select trigger_name, status from user_triggers
2 where table_name='TEST'
3 /
TRIGGER_NAME STATUS
------------------------------ --------
TEST_TRIGGER DISABLED
SQL>
However it won't do anything for foreign keys, or indeed any other constraint. That's because Oracle doesn't use triggers to enforce such things. Okay, under the covers constraints and user-defined triggers may share certain low-level kernel code. But at the level we're talking they are two different things.
但是，它不会对外键或任何其他约束做任何事情。那是因为 Oracle 不使用触发器来强制执行此类操作。好的，在幕后约束和用户定义的触发器可能共享某些低级内核代码。但在我们谈论的层面上，它们是两种不同的东西。
If you want to disable all foreign keys on a table I'm afraid you'll need to use something like this:
如果你想禁用表上的所有外键，恐怕你需要使用这样的东西：
SQL> select constraint_name, status from user_constraints
2 where table_name = 'EMP'
3 and constraint_type = 'R'
4 /
CONSTRAINT_NAME STATUS
------------------------------ --------
FK_DEPTNO ENABLED
SQL> begin
2 for r in ( select constraint_name, status from user_constraints
3 where table_name = 'EMP'
4 and constraint_type = 'R' )
5 loop
6 execute immediate 'alter table emp disable constraint '||r.constraint_name;
7 end loop;
8* end;
9 /
PL/SQL procedure successfully completed.
SQL> select constraint_name, status from user_constraints
2 where table_name = 'EMP'
3 and constraint_type = 'R'
4 /
CONSTRAINT_NAME STATUS
------------------------------ --------
FK_DEPTNO DISABLED
SQL>
This is the sort of thing you'll probably want to wrap in a user-defined function, which takes TABLE_NAME as a parameter. Also you'll need a similar function to re-enable the constraints.
这是您可能希望包装在用户定义函数中的那种东西，该函数将 TABLE_NAME 作为参数。您还需要一个类似的功能来重新启用约束。
