RE: Trigger question

Thanks,

I did have the trigger right - I am just confronted with an error that claims that my before insert or after insert trigger is not possible because the table is morphing:

The following error has occurred:

ORA-04091: table NETOPS.PAGERSCHEDULE is mutating, trigger/function may not see it
ORA-06512: at "NETOPS.MDATE_UPDATE", line 3 ORA-04088: error during execution of trigger 'NETOPS.MDATE_UPDATE'

Details:
ORA-04091: table NETOPS.PAGERSCHEDULE is mutating, trigger/function may not see it
ORA-06512: at "NETOPS.MDATE_UPDATE", line 3 ORA-04088: error during execution of trigger 'NETOPS.MDATE_UPDATE'

All I am trying to do is update an MDATE field on a table when a particular record is updated. I've always done this programatically. I was trying to do it in a trigger to make it mandatory and take one more thing away from the developers. I am pretty sure I am over simplifying this and in it's current incarnation I can't do this. I can probably store the update in another table and do a two step - do the update .. store the ID of the updated record elsewhere - then later on a schedule run a job that updates the primary table and purges the audit table... before that though I'll go with my programatic method of including that update in either a stored procedure or the SQL command. Unfortunately none of the tables in an inherited DB have CDATE or MDATE fields which makes a lot of data analysis very difficult. Seeing as the fields weren't there before and so now none of the front end code is expecting these new fields. Rather than retrofit this to all the code I'd rather add the functionality to the DB and keep it centralized .... Am I missing something in this - is this possible in a straghtforward way? Or is it going to have to be a convoluted process?

thanks again.

Adam

-----Original Message-----
From: Michael Netrusov [mailto:mn_at_g-fax.com] Sent: Tuesday, December 19, 2000 5:21 PM To: Multiple recipients of list ORACLE-L Subject: Re: Trigger question

Adam,

create or replace trigger "mike"."trt1" before update on "mike"."t1" for each row
begin

  null;

end;

To get a quick reference you can use say, Oracle DBA Studio, create the desired object with a gui and copy-paste the generated sql test.

HTH,
Michael

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 19, 2000 14:18

>
>
> Can someone send me a quick how-to for creating an on update trigger that
> updates a field that tracks record modification in a table - for each
> record. I am feeling extraordinarily braindead today .... i've done this
> in a prior life, but right now 8i and I aren't getting along. I am trying
> to retrofit some record change tracking ...just a cut and paste copy of
> working on-update pre record trigger code that I can see -
>
> An additional aside - can someone point me to a good basic examples site /
> book that might be usefull for this sort of thing? Oracle Docs seem to
have
> lots of manuals, but all I really need is to see a working block of code
> that I can disect and then understand this sort of stuff from then on.
>
>
>
> thanks
>
> adam
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Adam Turner
> INET: ATurner_at_concreteinc.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Michael Netrusov
  INET: mn_at_g-fax.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may