Mutating table error

[M]

Hello,

I have a very simple table, and want to create a trigger that updates
the date column entry (with the current date), whenever a row gets
modified.
Is there a simple way of fixing this, or would I have to create 3
extra triggers, a package, etc., as described in most posts about
mutating table errors?
Could you please explain to me WHY this error happens here (I have not
been able to find a clear explanation of what causes them)?


create table mvkTest
(bukva char(5),
kogda date);

insert into mvkTest
values('aaa', sysdate);
insert into mvkTest
values('bbb', sysdate);

create or replace trigger test_Trigger
after update on mvkTest
for each row
begin update mvkTest
set kogda = sysdate;
end;
/

update mvkTest
set bukva = 'ccc'
where bukva = 'aaa';

=======>>>>>>

update mvkTest
*
ERROR at line 1:
ORA-04091: table DTI.MVKTEST is mutating, trigger/function may not see
it
ORA-06512: at "DTI.TEST_TRIGG ER", line 1
ORA-04088: error during execution of trigger 'DTI.TEST_TRIGG ER'



Thank you,
G.

[Mark C. Stock]

Re: Mutating table error


"M" <greenandkind@y ahoo.comwrote in message
news:c7b3da1b.0 402171142.63994 4cb@posting.goo gle.com...
| Hello,
|
| I have a very simple table, and want to create a trigger that updates
| the date column entry (with the current date), whenever a row gets
| modified.
| Is there a simple way of fixing this, or would I have to create 3
| extra triggers, a package, etc., as described in most posts about
| mutating table errors?
| Could you please explain to me WHY this error happens here (I have not
| been able to find a clear explanation of what causes them)?
|
|
| create table mvkTest
| (bukva char(5),
| kogda date);
|
| insert into mvkTest
| values('aaa', sysdate);
| insert into mvkTest
| values('bbb', sysdate);
|
| create or replace trigger test_Trigger
| after update on mvkTest
| for each row
| begin update mvkTest
| set kogda = sysdate;
| end;
| /
|
| update mvkTest
| set bukva = 'ccc'
| where bukva = 'aaa';
|
| =======>>>>>>
|
| update mvkTest
| *
| ERROR at line 1:
| ORA-04091: table DTI.MVKTEST is mutating, trigger/function may not see
| it
| ORA-06512: at "DTI.TEST_TRIGG ER", line 1
| ORA-04088: error during execution of trigger 'DTI.TEST_TRIGG ER'
|
|
|
| Thank you,
| G.


are you used to SQL Server where you have the pseudo-tables for accessing
rows affected by the DML?

in Oracle you reference the values going into the database with the :NEW
'record' and the original values with the :OLD 'record'

so the trigger is more like

create or replace trigger test_trigger
before insert or update on mvktest
for each row
begin
:new.kogda := sysdate;
end;

simple, no?

your code, if it would work, would have updated every row in the table (no
where clause)

note:
[_] use a BEFORE trigger to set values before the DML is applied to the
database
[_] use 'INSERT or UPDATE' for a trigger that applies to both DML
[_] optionally use the INSERTING and UPDATING keywords (if inserting....)
for conditional logic in the same trigger

-- mcs

[VC]

Re: Mutating table error

Hello,

In

create or replace trigger test_Trigger
after update on mvkTest
for each row
begin update mvkTest
set kogda = sysdate;
end;

.... you are trying to update a table which is in the process of being
changed by the triggering statement and this causes the 'mutating' error.
For a discussion, see

http://asktom.oracle.com/pls/ask/f?p=4950:8:7998628199372151219::NO::F4950_P

8_DISPLAYID,F49 50_P8_CRITERIA: 9579487119866,


In your case, it's unclear why you need a trigger at all since you can just
use the default of 'SYSDATE' on the column in question...

VC

"M" <greenandkind@y ahoo.comwrote in message
news:c7b3da1b.0 402171142.63994 4cb@posting.goo gle.com...

Hello,
>
I have a very simple table, and want to create a trigger that updates
the date column entry (with the current date), whenever a row gets
modified.
Is there a simple way of fixing this, or would I have to create 3
extra triggers, a package, etc., as described in most posts about
mutating table errors?
Could you please explain to me WHY this error happens here (I have not
been able to find a clear explanation of what causes them)?
>
>
create table mvkTest
(bukva char(5),
kogda date);
>
insert into mvkTest
values('aaa', sysdate);
insert into mvkTest
values('bbb', sysdate);
>
create or replace trigger test_Trigger
after update on mvkTest
for each row
begin update mvkTest
set kogda = sysdate;
end;
/
>
update mvkTest
set bukva = 'ccc'
where bukva = 'aaa';
>
=======>>>>>>
>
update mvkTest
*
ERROR at line 1:
ORA-04091: table DTI.MVKTEST is mutating, trigger/function may not see
it
ORA-06512: at "DTI.TEST_TRIGG ER", line 1
ORA-04088: error during execution of trigger 'DTI.TEST_TRIGG ER'
>
>
>
Thank you,
G.

[Mark C. Stock]

Re: Mutating table error


"VC" <boston103@hotm ail.comwrote in message
news:iUxYb.2084 84$U%5.1156019@ attbi_s03...
| Hello,
|
| In
|
| create or replace trigger test_Trigger
| after update on mvkTest
| for each row
| begin update mvkTest
| set kogda = sysdate;
| end;
|
| ... you are trying to update a table which is in the process of being
| changed by the triggering statement and this causes the 'mutating' error.
| For a discussion, see
|

http://asktom.oracle.com/pls/ask/f?p=4950:8:7998628199372151219::NO::F4950_P

| 8_DISPLAYID,F49 50_P8_CRITERIA: 9579487119866,
|
|
| In your case, it's unclear why you need a trigger at all since you can
just
| use the default of 'SYSDATE' on the column in question...
|
| VC
|

actually, there are two issues with using a default value instead of a
trigger

1) it can be overridden on INSERT (including with an explicit null)
2) it only works on INSERT, not on UPDATE

a trigger is the only way to guarantee that a value is set, outside of
limiting all access to an API (for which there are many valid arguments)

-- mcs

[VC]

Re: Mutating table error

Hello,


"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message
news:UeqdnSRUc4 SVVa_dRVn_iw@co mcast.com...

>
"VC" <boston103@hotm ail.comwrote in message
news:iUxYb.2084 84$U%5.1156019@ attbi_s03...
| Hello,
|
| In
|
| create or replace trigger test_Trigger
| after update on mvkTest
| for each row
| begin update mvkTest
| set kogda = sysdate;
| end;
|
| ... you are trying to update a table which is in the process of being
| changed by the triggering statement and this causes the 'mutating'

error.

| For a discussion, see
|
>

http://asktom.oracle.com/pls/ask/f?p...9::NO::F4950_P

| 8_DISPLAYID,F49 50_P8_CRITERIA: 9579487119866,
|
|
| In your case, it's unclear why you need a trigger at all since you can
just
| use the default of 'SYSDATE' on the column in question...
|
| VC
|
>
actually, there are two issues with using a default value instead of a
trigger
>
1) it can be overridden on INSERT (including with an explicit null)
2) it only works on INSERT, not on UPDATE
>

Agree. I missed the update part ;)

a trigger is the only way to guarantee that a value is set, outside of
limiting all access to an API (for which there are many valid arguments)
>
-- mcs
>
>