Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> history triggers
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)
'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