代码之家 › 专栏 › 技术社区 › filippo

简单Oracle触发器

mutating-table ora-04091 triggers plsql oracle

0

filippo · 技术社区 · 15 年前

简单的一个。我对PLSQL和Oracle的错误消息有点陌生,从来没有太大帮助。

我想做一个简单的触发器,用当前日期更新一列,即表的“修改日期”列。但得到一个奇怪的错误。

这个主意很简单

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');

这会弹出错误:

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'

有人能快速解决这个问题吗?

2 回复 | 直到 15 年前

1

5

Rob van Wijk 15 年前

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'

托尼的建议几乎是正确的,但不幸的是,它没有汇编:

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

因为您只能在每行触发器之前在中更改新值:

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.

Rob。

2

Tony Andrews 15 年前

触发器应简单地读取:

CREATE OR REPLACE TRIGGER testTRG
BEFORE INSERT OR UPDATE ON test1
FOR EACH ROW
BEGIN 
     :new.tcol2 :=  to_char(sysdate);
END;

没有要求发布同一行的另一个更新(正如您发现的那样,您不能)。

通常使用日期列存储日期:

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;