Simple oracle triggers

Question

Hi there,

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.

Answer 1

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;

Answer 2

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.