mutating table error w/trigger

[robert]

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

[Alan Mills]

Re: mutating table error w/trigger


"robert" <gnuoytr@rcn.co m> wrote in message
news:da3c2186.0 307251102.22f5e 32f@posting.goo gle.com...[color=blue]
> 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[/color]

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.

[Alan Mills]

Re: mutating table error w/trigger


"robert" <gnuoytr@rcn.co m> wrote in message
news:da3c2186.0 307280515.5a3bd 30a@posting.goo gle.com...[color=blue]
> "Alan Mills" <Alan.Mills@xse rvices.pants.fu jitsu.com> wrote in message[/color]
news:<bg2r9r$2u 7$1@news.icl.se >...[color=blue][color=green]
> > "robert" <gnuoytr@rcn.co m> wrote in message
> > news:da3c2186.0 307251102.22f5e 32f@posting.goo gle.com...[color=darkred]
> > > 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[/color]
> >
> > I'd love to know what you're talking about. You seem to assume that I[/color][/color]
can[color=blue][color=green]
> > remember every thread there is on mutating tables or can be bothered to[/color][/color]
look[color=blue][color=green]
> > them all up. Ellaborate a little please.[/color]
>
>
> 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[/color]
wondering[color=blue]
> 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.
>[/color]

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.

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

:new.second_dat e_col := to_date(:new.or iginal_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.