Simple oracle triggers

Simple one. I´m a bit of a newvbie with PLSql and oracle's error messages are never too helpful.

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.

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:

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.

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:

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;