Re: mutating table error w/trigger

"Alan Mills" <Alan.Mills_at_xservices.pants.fujitsu.com> wrote in message news:<bg2r9r$2u7$1_at_news.icl.se>...
> "robert" <gnuoytr_at_rcn.com> wrote in message
> news:da3c2186.0307251102.22f5e32f_at_posting.google.com...
> > i've found the solution threads on changing a column on insert. works
> > fine.
> >
> > question:
> >
> > - will one package serve for all such triggers, or does there need to
> > be a package defined to support each table?
> >
> > my o'reilly book doesn't say, nor do the threads i found.
> >
> > thanks,
> > robert
>
> I'd love to know what you're talking about. You seem to assume that I can
> remember every thread there is on mutating tables or can be bothered to look
> them all up. Ellaborate a little please.

didn't mean to be obtuse. it seemed from reading the threads that the "mutating table error" was well known to those, unlike myself, who had been doing oracle for a while. the solutions in the various threads were the same:

i) - create a package to hold a an array of rowids ii) - create a before insert trigger to reset the array iii) - create an after insert for each row trigger to build an array

       of rowids of inserted rows
iv) - create an after insert trigger to parse the rows in the array,

      doing the work.

since i don't yet know the internals of oracle, or even for sure, where these triggers execute (in server process or client process), i'm wondering whether one package will suffice for any number of "sets" of the triggers. or whether it is necessary to create a package for each "set" of triggers.

this all arise because oracle, unlike DB2 for instance, won't allow a trigger to modify a column in an inserted row. in my case, one column is a "date" --> 12311998 which i want to turn into a sql date in a second column. seemed simple enough, until the mutating table error.

thanks,
robert Received on Mon Jul 28 2003 - 15:15:38 CEST