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 -> history triggers

history triggers

From: steph <stephan0h_at_gmx.net>
Date: 22 Feb 2006 09:18:46 -0800
Message-ID: <1140628726.576451.12260@g44g2000cwa.googlegroups.com>


Hi,

I want to setup history tables for some of my production tables. These tables should reflect all changes on the respective main tables. My idea was to locate these history tables in a separate schema, where every history table has exactly the same layout as the corresponding main table (each of these having columns logging creation dates and users already).

Next step would be to implement triggers for all of these tables capturing dml-operations. As I'm lazy I would like to have some kind of generic functionality. My idea was to have one main function containing the logic, which would be called from within every trigger:

create or replace procedure writehistrec(pTab in varchar2,pUser varchar2,pRowid in rowid) is
  cursor c_col (cpTab varchar2) is
    select column_name

      from user_tab_columns
     where table_name=upper(cpTab)

    ;
  l_insert varchar2(1000):='';
  l_columns varchar2(1000):='';
begin
  for r_col in c_col(pTab) loop
    if l_columns is null then
      l_columns:=r_col.column_name;
    else
      l_columns:=l_columns||','||r_col.column_name;     end if;
  end loop;
  l_insert:='insert into hist.'||pTab||' ('||l_columns||') '||

            'select '||l_columns||' from '||pUser||'.'||pTab||' where rowid=:1';
  --dbms_output.put_line(l_insert);
  execute immediate l_insert using pRowid; end writehistrec;

then my triggers would look something like this:

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.*).

Is there a way to solve this? Is this even a sane way to handle such a problem, or should I use a different approach?

Thanks,
Stephan Received on Wed Feb 22 2006 - 11:18:46 CST

Original text of this message

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