Oracle：更新时自动修改日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6440318/
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 : Automatic modification date on update
提问by Fredv
i have a table in oracle and i want to study the updates on lines
我在 oracle 中有一张表，我想研究在线更新
id number,
title varchar2(10),
modify_date date
i have created a trigger to feed the modify_date :
我创建了一个触发器来提供 modify_date ：
create or replace
TRIGGER schema.name_of_trigger
BEFORE UPDATE ON schema.name_of_table
FOR EACH ROW
BEGIN
:new.modify_date := sysdate;
END;
but when i make a big update from another table i would like the modify_date to be updated ONLY for lines with a new value, not all lines.
但是当我从另一个表进行大更新时，我希望仅针对具有新值的行而不是所有行更新 modify_date。
update mytable a set title = (select title from mytable2 b where b.id = a.id)
Is it possible ? i thought Oracle would not update a field with the same value
是否可以 ？我认为 Oracle 不会更新具有相同值的字段
Thanks
谢谢
回答by René Nyffenegger
You thought wrongly, Oracle does what you order it to do.
你想错了，Oracle 做你命令它做的事。
You can either try
你可以试试
update mytable a set title =
(select title from mytable2 b
where b.id = a.id and
b.title != a.title)
or change the trigger to specifically check for a different title name.
或更改触发器以专门检查不同的标题名称。
create or replace
TRIGGER schema.name_of_trigger
BEFORE UPDATE ON schema.name_of_table
FOR EACH ROW
BEGIN
-- Check for modification of title:
if :new.title != :old.title then
:new.modify_date := sysdate;
end if;
END;
回答by Rajesh Gupta
CREATE OR REPLACE TRIGGER TRG_AFT_UPD_MFD BEFORE UPDATE ON SUPER_TEST FOR EACH ROW
BEGIN
IF :NEW.MODIFIED_ON != SYSDATE THEN
:NEW.MODIFIED_ON := SYSDATE;
DBMS_OUTPUT.PUT_LINE('UPDATE :'||:NEW.MODIFIED_ON);
END IF;
END;
