oracle 仅触发更改的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11201045/
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
Trigger for only changed values
提问by Jaanna
Say we have 3 records in table: orig_tab
假设我们在表中有 3 条记录：orig_tab
---------------------------------------------
| PK | Name | Address | Postal Code |
---------------------------------------------
| 1 | AA | Street1 | 11111 |
| 2 | BB | Street2 | 22222 |
| 3 | CC | Street3 | 33333 |
---------------------------------------------
Now the data is changed:
现在数据改变了：
---------------------------------------------
| PK | Name | Address | Postal Code |
---------------------------------------------
| 1 | AA | Street1 | 11111 |
| 2 | BB | Street2 | 44444 |
| 3 | CC | Dtreet7 | 33333 |
---------------------------------------------
What client wants is the update records and only the updated columns (yes, I know it doesn't make any sense but they use some old system from 1970s and they want to do some logging etc.). So the reporting table should be like this:
客户想要的是更新记录和更新的列（是的，我知道这没有任何意义，但他们使用 1970 年代的一些旧系统，他们想做一些日志记录等）。所以报表应该是这样的：
---------------------------------------------
| PK | Name | Address | Postal Code |
---------------------------------------------
| 2 | | | 44444 |
| 3 | | Dtreet7 | |
---------------------------------------------
This what I tried:
这是我尝试过的：
CREATE OR REPLACE TRIGGER vr_reporting_trigger
AFTER UPDATE ON orig_tab
FOR EACH ROW
BEGIN
IF inserting THEN
INSERT INTO rep_tab(pk, name, address, code)
SELECT :new.pk, :new.name, :new.address, :new,code FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM rep_tab WHERE pk = :new.pk);
UPDATE rep_tab t SET t.name = :new.name, t.address = :new.address, t.code = :new.code
WHERE t.pk = :new.pk;
ELSIF updating THEN
IF :new.pk <> :old.pk THEN
UPDATE rep_tab t
SET t.name = :new.name, t.address = :new.address, t.code =: new.code
WHERE t.pk = :old.pk ;
END IF;
MERGE INTO rep_tab d
USING DUAL ON (d.pk = :old.pk)
WHEN MATCHED THEN
UPDATE SET d.name = :new.name, d.address = :new.address, d.code =: new.code
WHEN NOT MATCHED THEN
INSERT (d.pk,d.name, d.address, d.code) VALUES (:new.pk,:new.name, new.address, new.code);
END IF;
END;
with this solution, I get:
使用此解决方案，我得到：
---------------------------------------------
| PK | Name | Address | Postal Code |
---------------------------------------------
| 2 | BB | Street2 | 44444 |
| 3 | CC | Dtreet7 | 33333 |
---------------------------------------------
I know that it somewhere in insert claus in when updating statement but I can't figure out how to have this claus changed as per my requirement. Any suggestion?
我知道它在更新语句时插入子句中的某个地方，但我无法弄清楚如何根据我的要求更改此子句。有什么建议吗？
Thanks in advance.
提前致谢。
回答by Florin Ghita
You need this:
你需要这个：
In an UPDATE trigger, a column name can be specified with an UPDATING conditional predicate to determine if the named column is being updated. For example, assume a trigger is defined as the following:
在 UPDATE 触发器中，可以使用 UPDATING 条件谓词指定列名，以确定是否正在更新命名列。例如，假设触发器定义如下：
CREATE OR REPLACE TRIGGER ...
... UPDATE OF Sal, Comm ON Emp_tab ...
BEGIN
... IF UPDATING ('SAL') THEN ... END IF;
END;
回答by Barh
Trigger for only changed values.
仅触发更改的值。
As an example:
举个例子：
CREATE OR REPLACE TRIGGER trigger_department_update
BEFORE UPDATE OF
department_id,
cluster_id,
division_id,
macroregion_id,
address,
email
ON cl_department
FOR EACH ROW
BEGIN
IF (
:new.department_id != :old.department_id or
:new.cluster_id != :old.cluster_id or
:new.division_id != :old.division_id or
:new.macroregion_id != :old.macroregion_id or
:new.address != :old.address or
:new.email != :old.email
)
THEN :new.update_date := CURRENT_TIMESTAMP;
END IF;
END;
