To get around the mutating trigger, you need tp define a package that
contains a plsql table, varray (or whatever structure is appropiate) and a
function that does your select. Using a package essentially 'hides' from
Oracle that you are updating/reading from the same table.
Then in a before insert/update/delete row level trigger you populate the
plsql table that you defined in that package. Then in an after statement
level trigger you walk through your plsql table and then use the package
function to select your data and insert it into your other table.
--
Terry Dykstra
Canadian Forest Oil Ltd.
"Joachim Zobel" <jz-2004_at_heute-morgen.de> wrote in message
news:pan.2005.02.03.07.48.32.910947_at_heute-morgen.de...
>
> Hi.
>
> To do historisation I copy each row to an identical table in a before
> trigger. This is working well for one table at the moment. However I have
> to do a very ugly
>
> INSERT INTO the_table_past(field1, field2,..)
> VALUES (:old.field1, :old.field2, ...);
>
> which explicitely names all fields (about 50). Doing an
>
> INSERT INTO the_table_past SELECT * FROM the_table;
>
> is not possible since it would read a mutating table.
>
> Now I would like to do the same for more tables and I do want to avoid
> copying of the source code if possible. Is there a way to write a generic
> copy row function for usage inside a trigger, where the table is used as a
> parameter?
>
> Thanks,
> Joachim
>
> --
> Warnung: \" kann Augenkrebs verursachen.
>
Received on Fri Feb 04 2005 - 09:54:35 CST
