禁用触发器并重新启用触发器，但同时避免更改表 [英] Disable triggers and re-enable triggers but avoid table alteration in meantime

问题描述

我有以下情况: 表(MyTable)应该通过批处理(调用myplsql()过程)进行处理(更新/插入/删除等).

I have the following situation: A table (MyTable) should be processed (updates/inserts/deletes etc) by a batch process (a call to a myplsql() procedure).

在执行myplsql期间，任何人都不能触摸MyTable-因此MyTable被myplsql锁定为互斥模式.

During myplsql execution no one should touch MyTable - so MyTable is locked in exclusive mode by myplsql.

现在MyTable定义了许多on insert，on update，on delete触发器，但是在执行批处理时不需要这些触发器-而且它们极大地减慢了批处理的速度.

Now MyTable has a number of on insert,on update, on delete triggers defined but those are not needed while performing batch processing - moreover they slow down the batch process extremely.

因此解决方案是在调用myplsql()之前禁用触发器.

So the solution is to disable the triggers before calling myplsql().

但是如何避免在执行alter table ... disable trigger之后和myplsql设法锁定表之前有人触摸MyTable， 假设alter table执行隐式提交-那么在此之前获得的任何锁都将丢失?

But how to avoid someone touching the MyTable just after alter table ... disable trigger is performed and before myplsql manages to lock the table, given that alter table performs implicit commit - so any lock acquired before that will be lost anyway?

部分问题是我无法控制可能试图触摸表格的其他代码或其他用户.

Part of the problem is that I do not control the other code or the other user that could try to touch the Table.

简而言之，我需要一次完成以下操作:

In a few words I need to perform the following in a single shot:

Lock MyTable 
Disable Triggers (somehow without loosing the lock)
Process MyTable
Enable Triggers
Unlock MyTable  

一个想法是从表中删除授权-并使其他用户无法使用它.

One thought was to remove grants from the table - and render it unusable for other users.

但是事实证明-这不是一个选择，因为其他进程/用户执行以表所有者用户身份登录的操作.

But as it is turns out - that is not an option as the other processes/users perform their operations logged in as a table owner user.

谢谢.

推荐答案

一种稍有不同的方法是通过添加when子句，使触发器保持启用状态，但减小(如果不是完全消除)其影响，请执行以下操作:

A slightly different approach is to keep the triggers enabled but reduce (if not quite entirely remove) their impact, by adding a when clause something like:

create or replace trigger ...
...
for each row
when (sys_context('userenv', 'client_info') is null
   or sys_context('userenv', 'client_info') != 'BATCH')
declare
...
begin
...
end;
/

然后在您的过程中添加呼叫一开始作为禁用触发器"步骤:

Then in your procedure add a call at the start as your 'disable triggers' step:

dbms_application_info.set_client_info('BATCH');

并在最后将其清除，以防万一会话保持活动状态并被重用(因此，您可能也想在异常处理程序中执行此操作):

and clear it again at the end, just in case the session is left alive and reused (so you might want to do this in an exception handler too):

dbms_application_info.set_client_info(null);

您还可以使用模块，操作或组合.设置到位后，触发器仍将被评估但不会触发，因此内部发生的任何事情都将被跳过-触发器主体不运行，如

You could also use module, or action, or a combination. While that setting is in place the trigger will still be evaluated but won't fire, so any thing happening inside will skipped - the trigger body does not run, as the docs put it.

这并不是万无一失的，因为没有什么可以真正阻止其他用户/应用程序进行相同的调用，但是，如果您选择更具描述性的字符串和/或设置的组合，则必须进行仔细考虑-我想您大多是担心事故而不是坏演员.

This isn't foolproof as there is nothing really stopping other users/applications making the same calls, but if you pick a more descriptive string and/or a combination of settings, it would have to be deliberate - and I think you're mostly worried about accidents not bad actors.

使用无意义的触发器进行快速速度测试，只会使事情放慢一点.

Quick speed test with a pointless trigger that does just slows things down a bit.

create table t42 (id number);

-- no trigger
insert into t42 (id) select level from dual connect by level <= 10000;

10,000 rows inserted.

Elapsed: 00:00:00.050

create or replace trigger tr42 before insert on t42 for each row
declare
  dt date;
begin
  select sysdate into dt from dual;
end;
/

-- plain trigger
insert into t42 (id) select level from dual connect by level <= 10000;

10,000 rows inserted.

Elapsed: 00:00:00.466

create or replace trigger tr42 before insert on t42 for each row
when (sys_context('userenv', 'client_info') is null
   or sys_context('userenv', 'client_info') != 'BATCH')
declare
  dt date;
begin
  select sysdate into dt from dual;
end;
/

-- userenv trigger, not set
insert into t42 (id) select level from dual connect by level <= 10000;

10,000 rows inserted.

Elapsed: 00:00:00.460

- userenv trigger, set to BATCH

exec dbms_application_info.set_client_info('BATCH');

insert into t42 (id) select level from dual connect by level <= 10000;

10,000 rows inserted.

Elapsed: 00:00:00.040

exec dbms_application_info.set_client_info(null);

进行远程调用有一些差异，但我跑了几次，很明显，使用普通触发器运行与不设置BATCH的受限触发器运行非常相似，并且两者都比不使用BATCH进行运行要慢得多触发或设置了BATCH的约束触发.在我的测试中，存在一个数量级的差异.

There's a bit of variation from making remote calls, but I ran a few times and it's clear that running with a plain trigger is very similar to running with the constrained trigger without BATCH set, and both are much slower than running without a trigger or with the constrained trigger with BATCH set. In my testing there's an order of magnitude difference.

这篇关于禁用触发器并重新启用触发器，但同时避免更改表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！