更新列时的 Oracle SQL 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7982052/
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 SQL trigger on update of column
提问by Jamie Taylor
I'm trying to create a trigger for my table which automatically adds a published date based on when a certain flag is set to 'Y'
我正在尝试为我的表创建一个触发器，它会根据某个标志设置为“Y”的时间自动添加发布日期
I don't have much experience with creating triggers but so far this is what i've got
我在创建触发器方面没有太多经验，但到目前为止，这就是我所拥有的
create or replace
TRIGGER ADD_CREATE_DT
after UPDATE of approved ON articles
for each row
BEGIN
:new.create_dt := sysdate where approved = 'Y';
END;
I get this error when updating the column
更新列时出现此错误
trigger 'USER.ADD_CREATE_DT' is invalid and failed re-validation
触发器 'USER.ADD_CREATE_DT' 无效且重新验证失败
Any ideas?
有任何想法吗？
Thanks
谢谢
回答by Tony Andrews
Use the WHEN clause:
使用 WHEN 子句：
create or replace
TRIGGER ADD_CREATE_DT
after UPDATE of approved ON articles
for each row
when (new.approved = 'Y')
BEGIN
:new.create_dt := sysdate;
END;
Or use IF:
或者使用如果：
create or replace
TRIGGER ADD_CREATE_DT
after UPDATE of approved ON articles
for each row
BEGIN
if :new.approved = 'Y' then
:new.create_dt := sysdate;
end if;
END;
In this case, WHEN is more appropriate and efficient.
在这种情况下，WHEN 更为合适和高效。
回答by schurik
create or replace
TRIGGER ADD_CREATE_DT
after UPDATE of approved ON articles
for each row
BEGIN
IF :NEW.approved = 'Y' THEN
:new.create_dt := sysdate;
END IF;
END;
回答by Jeff_Alieffson
I don't know What version of Oracle do you use? In Oracle 10g I got the following error:
不知道你用的是什么版本的oracle？在 Oracle 10g 中，我收到以下错误：
ORA-04084: cannot change NEW values for this trigger type
04084. 00000 - "cannot change NEW values for this trigger type"
*Cause: New trigger variables can only be changed in before row
insert or update triggers.
*Action: Change the trigger type or remove the variable reference.
It does not allow to change the field on AFTER triggers.
它不允许更改 AFTER 触发器上的字段。
