Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: history triggers
comments embedded
On 22 Feb 2006 09:18:46 -0800, "steph" <stephan0h_at_gmx.net> wrote:
>create or replace trigger plates_hist_trg
> before update or delete on plates
> for each row
>begin
> hist.writehistrec('plates',user,:new.rowid);
>end plates_hist_trg;
>
>
>unfortuately this leads to a mutating-trigger error. So I'm stuck,
>because I can't think of any way to generically transfer the trigger's
>record information (:new.*).
You can't treat :new as a record.
>
>Is there a way to solve this?
Yes.
Set up a package.
This package has a *sub*type definition for every table bound to
<table_name>%rowtype.
It also has a procedure for every table with only one parameter of
<subtype>
and only one insert statement
insert into <audit> values <rec>
where rec is of <subtype>
In your trigger you still need to assign individual columns to the
record fields
but you can write pl/sql to generate pl/sql.
Is this even a sane way to handle such a
>problem, or should I use a different approach?
What you do is insane as you have dynamic sql in your trigger -> (hard) parse for every trigger call.
I'm not going to give away more. I just developed something which I basically described above, and it works like a charm. It requires 9i or higher.
-- Sybrand Bakker, Senior Oracle DBAReceived on Wed Feb 22 2006 - 11:48:32 CST