Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: history triggers

Re: history triggers

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 22 Feb 2006 18:48:32 +0100
Message-ID: <ki8pv194ua256mfhcgq8b3pcrdj061bqtg@4ax.com>

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 DBA
Received on Wed Feb 22 2006 - 11:48:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US