Re: mutating table error w/trigger

"robert" <gnuoytr_at_rcn.com> wrote in message news:da3c2186.0307280515.5a3bd30a_at_posting.google.com... > "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

> 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.
>

From your last paragraph it looks like you are simply denomralising the date value into another column in the same record.

If so then why not just have your BEFORE INSERT trigger say something like

:new.second_date_col := to_date(:new.original_date_col, 'MMDDYYYY');

other than that I think I might be personally inclined to have seperate packages, for each table. It modularises the code better. Received on Mon Jul 28 2003 - 15:41:52 CEST