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;

虽然效率可能不是很高！

Probably not terribly efficient though!

这篇关于Oracle:排除用于触发触发器的一列的更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！