简单的 oracle 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3699508/
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
Simple oracle triggers
提问by filippo
Simple one. I′m a bit of a newvbie with PLSql and oracle's error messages are never too helpful.
简单的一个。我是 PLSql 的新手，oracle 的错误消息从来没有太大帮助。
I want to do a simple trigger to update a column with the current date i.e. 'modified date' column of a table. Getting an odd error though.
我想做一个简单的触发器来更新具有当前日期的列，即表的“修改日期”列。虽然得到一个奇怪的错误。
The idea is simple
想法很简单
create table test1 (tcol varchar2(255), tcol2 varchar2(255))
CREATE OR REPLACE TRIGGER testTRG
AFTER INSERT OR UPDATE ON test1
FOR EACH ROW
BEGIN
update test1
set tcol2 = to_char(sysdate)
where tcol = :OLD.tcol;
END;
insert into test1 (tcol) values ('test1');
this pops up the error:
这会弹出错误：
ORA-04091: table RAIDBIDAT_OWN.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "RAIDBIDAT_OWN.TESTTRG", line 2
ORA-04088: error during execution of trigger 'RAIDBIDAT_OWN.TESTTRG'
Would anyone have a quick solution for this?
有人会对此有快速解决方案吗？
cheers,
干杯，
f.
F。
回答by Rob van Wijk
Your situation:
你的情况：
SQL> create table test1 (tcol varchar2(255), tcol2 varchar2(255))
2 /
Table created.
SQL> CREATE OR REPLACE TRIGGER testTRG
2 AFTER INSERT OR UPDATE ON test1
3 FOR EACH ROW
4 BEGIN
5 -- Your original trigger
6 update test1
7 set tcol2 = to_char(sysdate)
8 where tcol = :OLD.tcol;
9 END;
10 /
Trigger created.
SQL> insert into test1 (tcol) values ('test1');
insert into test1 (tcol) values ('test1')
*
ERROR at line 1:
ORA-04091: table [schema].TEST1 is mutating, trigger/function may not see it
ORA-06512: at "[schema].TESTTRG", line 3
ORA-04088: error during execution of trigger '[schema].TESTTRG'
Tony's suggestion is almost right, but unfortunately it doesn't compile:
托尼的建议几乎是正确的，但不幸的是它没有编译：
SQL> CREATE OR REPLACE TRIGGER testTRG
2 AFTER INSERT OR UPDATE ON test1
3 FOR EACH ROW
4 BEGIN
5 -- Tony's suggestion
6 :new.tcol2 := sysdate;
7 END;
8 /
CREATE OR REPLACE TRIGGER testTRG
*
ERROR at line 1:
ORA-04084: cannot change NEW values for this trigger type
Because you can only change NEW values in before-each-row triggers:
因为您只能在 before-each-row 触发器中更改 NEW 值：
SQL> create or replace trigger testtrg
2 before insert or update on test1
3 for each row
4 begin
5 :new.tcol2 := sysdate;
6 end;
7 /
Trigger created.
SQL> insert into test1 (tcol) values ('test1');
1 row created.
SQL> select * from test1
2 /
TCOL
------------------------------------------------------------------------------------------
TCOL2
------------------------------------------------------------------------------------------
test1
13-09-2010 12:37:24
1 row selected.
Regards, Rob.
问候，罗伯。
回答by Tony Andrews
The trigger should simply read:
触发器应该简单地阅读：
CREATE OR REPLACE TRIGGER testTRG
BEFORE INSERT OR UPDATE ON test1
FOR EACH ROW
BEGIN
:new.tcol2 := to_char(sysdate);
END;
There is no requirement to issue another update of the same row (and as you have found, you cannot).
不需要发出同一行的另一个更新（正如您所发现的，您不能）。
It is more usual to use DATE columns to store dates:
更常用的是使用 DATE 列来存储日期：
create table test1 (tcol varchar2(255), tcol2 date);
CREATE OR REPLACE TRIGGER testTRG
BEFORE INSERT OR UPDATE ON test1
FOR EACH ROW
BEGIN
:new.tcol2 := sysdate;
END;
