plsql跑触发器脚本会一直执行_禁用触发器并重新启用触发器，但同时避免更改表...

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).

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

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.

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

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.

Thanks.

解决方案

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);

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.