Re: Mutating table error

"M" <greenandkind_at_yahoo.com> wrote in message news:c7b3da1b.0402171142.639944cb_at_posting.google.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_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