"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...
"Alan Mills" <Al********@xservices.pants.fujitsu.com> wrote in message
news:<bg**********@news.icl.se>... "robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om... 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.
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.
