Mutating trigger Error

[tedlaraghu]

Hi every one,
I am trying to update time_added column when ever there is an update on the table.
ALTER TABLE emp
ADD (time_added timestamp);

UPDATE emp
SET time_added = sysdate;

When ever i issue any update stmt like these,automatically time_added column should also be update.
I tried creating a trigger,but I could acheive the expected result.
UPDATE emp
SET deptno = 11
WHERE deptno = 10;

Please can you give me the trigger code to acheive my task, or do we have any other alternatives.

Regards
Raghu

[debasisdas]

please post your code with the table structure .

[tedlaraghu]

SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
DEPTNAME VARCHAR2(50)
TIME_ADDED TIMESTAMP(6)

It is a default oracle table, so i am not providing data

[debasisdas]

Along with the table structure , I have asked you to post your code for my recerence and check the same .

Where is the code ?

[tedlaraghu]

create or replace trigger upd_time
after update on emp
for each row
begin
update emp
set time_added = sysdate;
end;

update emp
set sal = 10000
where deptno =11;

ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.UPD_TIME", line 2
ORA-04088: error during execution of trigger 'SCOTT.UPD_TIME'

[dmanojbaba]

i have a table with values like 1,2,3.... (primary key)
if i delete a row eg.4,
i need my trigger to update the values 5 to 4,6 to 5, 7 to 6,.. like that, after deleting 4.
pls help me...

my table ABC with QNO as primary key.

my trigger
create or replace trigger t1 after delete on abc
referencing old as orow
for each row
declare
begin
update abc set qno=qno-1 where qno>:orow.qno;
end;
Trigger Created

but when i exec the command
delete abc where qno=3;

i get the error msg
delete qb1 where qno=2
*
ERROR at line 1:
ORA-04091: table RECCSE.ABC is mutating, trigger/function may not see it
ORA-06512: at "RECCSE.T1", line 4
ORA-04088: error during execution of trigger 'RECCSE.T1'


pls help me.....:(
--------------------------------------------------------------------------------

[debasisdas]

create or replace trigger upd_time
after update on emp
for each row
begin
update emp
set time_added = sysdate;
end;

update emp
set sal = 10000
where deptno =11;

ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.UPD_TIME", line 2
ORA-04088: error during execution of trigger 'SCOTT.UPD_TIME'

in this case the trigger is mutating because it is trying to update the base table from which it is reading data.

[debasisdas]

i have a table with values like 1,2,3.... (primary key)
if i delete a row eg.4,
i need my trigger to update the values 5 to 4,6 to 5, 7 to 6,.. like that, after deleting 4.
pls help me...

my table ABC with QNO as primary key.

my trigger
create or replace trigger t1 after delete on abc
referencing old as orow
for each row
declare
begin
update abc set qno=qno-1 where qno>:orow.qno;
end;
Trigger Created

but when i exec the command
delete abc where qno=3;

i get the error msg
delete qb1 where qno=2
*
ERROR at line 1:
ORA-04091: table RECCSE.ABC is mutating, trigger/function may not see it
ORA-06512: at "RECCSE.T1", line 4
ORA-04088: error during execution of trigger 'RECCSE.T1'

--------------------------------------------------------------------------------

same problem in this case also.

[tedlaraghu]

Can you give us work arounds

[debasisdas]

Please find the details regarding this problem and realted solutions here and here.