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_TRIGGER", line 1
ORA-04088: error during execution of trigger 'DTI.TEST_TRIGGER'

Thank you,
G.

[Mark C. Stock]

"M" <gr**********@yahoo.com> wrote in message
news:c7**************************@posting.google.c om...
| 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_TRIGGER", line 1
| ORA-04088: error during execution of trigger 'DTI.TEST_TRIGGER'
|
|
|
| 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]

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,F4950_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" <gr**********@yahoo.com> wrote in message
news:c7**************************@posting.google.c om...

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_TRIGGER", line 1
ORA-04088: error during execution of trigger 'DTI.TEST_TRIGGER'

Thank you,
G.

[Mark C. Stock]

"VC" <bo*******@hotmail.com> wrote in message
news:iUxYb.208484$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,F4950_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]

Hello,
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:Ue********************@comcast.com...

"VC" <bo*******@hotmail.com> wrote in message
news:iUxYb.208484$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,F4950_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