Oracle:排除触发触发器的一列更新 [英] Oracle: excluding updates of one column for firing a trigger
问题描述
在oracle中,我可以指定列,这将引发触发器的触发:
In oracle I can specify the columns, which should induce a firing of a trigger:
create or replace trigger my_trigger
before update of col1, col2, col3 on my_table for each row
begin
// the trigger code will be executed only if col1 or col2 or col3 was updated
end;
现在,我想执行以下操作:当仅一个列被更新时,我不希望触发触发器.这怎么可能?
Now I want to do the following: I don't want the trigger to fire, when only one column was updated. How is this possible?
我可以列出除那一列之外的所有列,该列不应引起触发器的触发.对于具有许多列的表,这是非常麻烦的.
I could list all columns except the one, which should not induce a firing of the trigger. This is quite cumbersome for tables with many columns.
另一种方法是使用像这样的UPDATING功能:
Another way would be to use the UPDATING function like this:
if not updating('COL3') then ...
但是,如果我立即更改了COL1 和 COL3,则该语句的计算结果为false.这不是我想要的,因为我想限制仅一个列(COL3)更新时的执行.
But if I changed COL1 and COL3 at once, the statement evaluates to false. That's not what I want since, I want to restrict the execution when only one column (COL3) was updated.
推荐答案
您可以执行以下操作:
create or replace trigger my_trigger
before update on my_table
for each row
declare
n_cols integer := 0;
begin
for r in (select column_name from all_tab_columns
where table_name = 'MY_TABLE'
and owner = 'MY_SCHEMA')
loop
if updating(r.column_name) then
n_cols := n_cols + 1;
exit when n_cols > 1;
end if;
end loop;
if n_cols > 1 then
do_something;
end if;
end;
可能效率不高!
这篇关于Oracle:排除触发触发器的一列更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!