Re: mutating table error w/trigger

"Alan Mills" <Alan.Mills_at_xservices.pants.fujitsu.com> wrote in message news:<bg399r$9sb$1_at_news.icl.se>...
> "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
 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.
> >
>
> OK thanks for the ellaboration.
>
> From your last paragraph it looks like you are simply denomralising the date
> value into another column in the same record.

sort of. the row gets created in a Mutant Bastard Child of Prolog prop. language. i've no control of the insert data, thus using a trigger to make the real date column. the end user will then be able to use sql report generators.

>
> 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');

that's what i tried, and got the mutating table error (unless one was :new and the other :old <G>)

>
> other than that I think I might be personally inclined to have seperate
> packages, for each table. It modularises the code better.

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