SQL Oracle：排除用于触发触发器的一列的更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1421645/
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
Oracle: excluding updates of one column for firing a trigger
提问by Theo Lenndorff
In oracle I can specify the columns, which should induce a firing of a trigger:
在 oracle 中，我可以指定列，这应该会引发触发器的触发：
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 onecolumn 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.
我可以列出除列之外的所有列，这些列不应引起触发器的触发。这对于有很多列的表来说是相当麻烦的。
Another way would be to use the UPDATING function like this:
另一种方法是像这样使用 UPDATING 函数：
if not updating('COL3') then ...
But if I changed COL1 andCOL3 at once, the statement evaluates to false. That's not what I want since, I want to restrict the execution when only onecolumn (COL3) was updated.
但是，如果我一次更改 COL1和COL3，则该语句的计算结果为 false。这不是我想要的，因为我想在仅更新一列（COL3）时限制执行。
回答by Tony Andrews
You could do something like this:
你可以这样做：
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!
虽然可能不是非常有效！
回答by Fersca
I had the same problem yesterday. I wanted to code a trigger that fired on every field except one, the table had 103 colums.
我昨天遇到了同样的问题。我想编写一个触发器，该触发器在除一个字段之外的每个字段上触发，该表有 103 列。
First I coded:
首先我编码：
if (:OLD.col1<>:NEW.col1 or :OLD.col2<>:NEW.col2 or :OLD.col3<>:NEW.col3 ....)
But I had some problems with null values, so I added:
但是我对空值有一些问题，所以我补充说：
if (NVL(:OLD.col1,0)<>NVL(:NEW.col1,0) or NVL(:OLD.col2,0)<>NVL(:NEW.col2,0) ....)
But then I had some problems with DATE columns, it became a mess..
但是后来我在 DATE 列上遇到了一些问题，它变得一团糟..
I think that the best solution is to list all columns that you want to verify in the "OF":
我认为最好的解决方案是在“OF”中列出要验证的所有列：
AFTER INSERT OR UPDATE of cOL1, col2, col3 ... colN ON table1
It was not "elegant" but... it worked perfect.
它不是“优雅”，但是......它工作得很好。
回答by APC
It's probably not the answer you want to hear, but I think you are rather over-exaggerating the burden of maintenance. It is not normal for a table's structure to change very often after it goes into production. If you do have a table which is subject to frequent changes of column number or name, then I would suggest you have a bigger, architectural problem.
这可能不是您想听到的答案，但我认为您过于夸大了维护的负担。表的结构在投入生产后经常更改是不正常的。如果您确实有一个经常更改列号或名称的表，那么我建议您有一个更大的架构问题。
So, just type out all the column names now, and wait to see whether maintanance becomes an issue. It is certainly not worth coding a complicated implementation in a trigger - a tax which you will pay on every single update - in order to avoid an occasional changes to the DDL script.
因此，现在只需输入所有列名，然后等待维护是否成为问题。为了避免偶尔更改 DDL 脚本，在触发器中编写复杂的实现当然是不值得的 - 您将在每次更新时支付一笔税款。
回答by Jeremy Bourque
I don't think there's a way you can avoid having to list all of the other columns in the table, either in the trigger body or else in the before update of ...clause.
我认为没有一种方法可以避免在触发器主体或before update of ...子句中列出表中的所有其他列。
However, you might be able to write an alter trigger on the table to regenerate the update trigger automatically if any columns are added or removed. It's a little more work, but then the maintenance should be automatic.
但是，如果添加或删除任何列，您可能能够在表上编写一个更改触发器以自动重新生成更新触发器。这需要更多的工作，但是维护应该是自动的。
