By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,569 Members | 1,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,569 IT Pros & Developers. It's quick & easy.

Mutating trigger Error

P: 15
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
Aug 2 '07 #1
Share this Question
Share on Google+
9 Replies


debasisdas
Expert 5K+
P: 8,127
please post your code with the table structure .
Aug 3 '07 #2

P: 15
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
Aug 3 '07 #3

debasisdas
Expert 5K+
P: 8,127
Along with the table structure , I have asked you to post your code for my recerence and check the same .

Where is the code ?
Aug 4 '07 #4

P: 15
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'
Aug 4 '07 #5

P: 5
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.....:(
--------------------------------------------------------------------------------
Aug 7 '07 #6

debasisdas
Expert 5K+
P: 8,127
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.
Aug 7 '07 #7

debasisdas
Expert 5K+
P: 8,127
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.
Aug 7 '07 #8

P: 15
Can you give us work arounds
Aug 8 '07 #9

debasisdas
Expert 5K+
P: 8,127
Please find the details regarding this problem and realted solutions here and here.
Aug 8 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.