Re: Newbie: Update trigger

Hi Alessandro,

news.green.ch schrieb:
> Hi everyone
> my name is Alessandro and i'm new to this newsgroup.
>
> I have a simple problem. I would like in an update trigger to update a
> timestamp and moduser field to take trace of the changes to a row.
> But i having troubles because Oracle says the table is mutating.
> The trigger is defined like that:
> CREATE OR REPLACE TRIGGER FC_UTRICHIESTA_U_INFOS BEFORE UPDATE ON
> IASTEST.FC_UTRICHIESTA
> FOR EACH ROW
> BEGIN
> UPDATE FC_UTRICHIESTA SET MUTADATA = SYSDATE;
> END;
> /

What you try to do here is to update all rows in your table each time you update one row in your table. So for each updated record in your table, your trigger will fire again and try to update all rows .....

Oracle prevents such recursive firing of a trigger and you get the 'mutating table' error message.

I assume you want to update the MUTADATA column only for the updated rows:

create or replace trigger FC_UTRICHIESTA_U_INFOS   after update on IASTEST.FC_UTRICHIESTA   for each row
begin
  update FC_UTRICHIESTA
    set MUTADATA = SYSDATE
    where <primary key column> = :new.<primary key column>; end;
/

-- 
All emails sent to this address are never read and never will be
answered. Sorry, but until someone cleans up the spam mess, that's the
way it has to be.

E-Mails, die direkt an diese Adresse geschickt werden, lese und
beantworte ich nicht. Ich bedauere diesen Umstand sehr, kenne derzeit
aber keine bessere Möglichkeit, um die Spam-Flut abzustellen.

Mit freundlichen Grüßen / Best Regards
Michael Gast
SEPP MED GmbH