从触发器 Oracle 禁用触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15043135/
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
Disable a trigger from a trigger Oracle
提问by Dave Clarke
I've stumbled on a situation where I need to disable a trigger from a trigger before doing an update, and then renable it.
我偶然发现了一种情况，我需要在执行更新之前从触发器中禁用触发器，然后再重新启用它。
Basically, I have two tables:
基本上，我有两个表：
TIME_SLOTShas fields such as start time, end time, to set the time slot for a programme as well as programme ID (foreign key) to specify which program.PROGRAMMEScontains a list of all the different available programs & their details. Also contains a duration.
TIME_SLOTS具有开始时间、结束时间等字段，用于设置节目的时间段以及节目 ID（外键）以指定哪个节目。PROGRAMMES包含所有不同可用程序及其详细信息的列表。还包含一个持续时间。
I have an existing trigger that, when updating or inserting to TIME_SLOTS, the trigger lookups the duration from PROGRAMMESand ensures that End Time = Start Time + Duration.
我有一个现有的触发器，在更新或插入到 时TIME_SLOTS，触发器查找持续时间PROGRAMMES并确保结束时间 = 开始时间 + 持续时间。
I also want to add a new trigger that updates the End Time in TIME_SLOTSwhen changing the duration in PROGRAMMES.
我还想添加一个新触发器，在TIME_SLOTS更改PROGRAMMES.
I have set these two triggers up, but when changing the duration I get:
我已经设置了这两个触发器，但是在更改持续时间时我得到：
One error saving changes to table "SE217"."PROGRAMMES":
Row 1: ORA-04091: table SE217.PROGRAMMES is mutating, trigger/function may not see it
ORA-06512: at "SE217.SCHEDULES_VALID_TIMES", line 19
ORA-04088: error during execution of trigger 'SE217.SCHEDULES_VALID_TIMES'
ORA-06512: at "SE217.UPDATE_END_TIME", line 5
ORA-04088: error during execution of trigger 'SE217.UPDATE_END_TIME'
This is obviously because when I change the duration, the 2nd trigger goes to update the end time in TIME_SLOTS. The trigger on TIME_SLOTSfires and looks up the duration - the duration is mutating and I get the error as above.
这显然是因为当我更改持续时间时，第二个触发器会更新TIME_SLOTS. 触发器TIME_SLOTS触发并查找持续时间 - 持续时间正在发生变化，我得到了上述错误。
It seems to me that when I update the TIME_SLOTSrow with the newly calculated end time, I should just disable the trigger before and renable after the update - but as this is trigger I can't alter a trigger...
在我看来，当我TIME_SLOTS使用新计算的结束时间更新行时，我应该在更新之前禁用触发器并在更新后重新启用 - 但由于这是触发器，我无法更改触发器......
Any ideas?
有任何想法吗？
EDIT: I had a thought that I could set a global variable and check this var in the trigger that I don't want to run etc - but wasn't sure how best to implement?
编辑：我有一个想法，我可以设置一个全局变量并在我不想运行的触发器中检查这个变量等等 - 但不确定如何最好地实现？
回答by Ben
You can almost certainly disable one trigger from another using an EXECUTE IMMEDIATEstatement:
您几乎可以肯定地使用EXECUTE IMMEDIATE语句从另一个触发器中禁用一个触发器：
EXECUTE IMMEDIATE 'ALTER TRIGGER trigger_name_here DISABLE';
However, you definitely shouldn't be using triggers for application logic. It's a messy business, not least due to the fact that triggers aren't guaranteed to fire in order, but also because of the kind of "problem" you're experiencing.
但是，您绝对不应该为应用程序逻辑使用触发器。这是一项混乱的业务，不仅是因为不能保证触发器按顺序触发，而且还因为您遇到了那种“问题”。
It would be much easier and significantly safer to move allof the functionality you described to a stored procedure or package, and use triggers only where necessary for validation purposes.
将您描述的所有功能移动到存储过程或程序包中，并且仅在必要时使用触发器进行验证会更容易也更安全。
回答by Michael
Those kinds of problems occurs when you have to customize an existing functionality and you just have full control on database. So you are not able to replace the inserts/updates by a procedure, you can just react. In this situation you have triggers on both tables and propagate values between the tables in both directions.
当您必须自定义现有功能并且您只是完全控制数据库时，就会出现这些类型的问题。因此，您无法通过过程替换插入/更新，您只能做出反应。在这种情况下，您在两个表上都有触发器，并在两个表之间双向传播值。
