Re: History functionality
Date: Sat, 13 Mar 2004 11:06:59 +0100
Message-ID: <4052dd10$0$310$636a15ce_at_news.free.fr>
"Ed Avis" <ed_at_membled.com> a écrit dans le message de news:l1smgk4pph.fsf_at_budvar.future-i.net...
> "Michel Cadot" <micadot{at}altern{dot}org> writes:
>
> >>Now i need to have a history funtionality for a few of my
> >>tables. This must include the old values, time and date of change
> >>and user who changed the value.
>
> >Have a look at triggers:
>
> Yes, triggers are the way to do it. But it seems that wanting history
> on tables is very common and perhaps even the main use of triggers.
> Given a table definition it is possible to generate a history table
> and triggers to update the history. Is there a program to generate
> these definitions automatically?
>
> --
> Ed Avis <ed_at_membled.com>
I don't know such a program but i write the followng one in a couple of hours. I admit it is not very efficient and it does not work for long, user defined types, object types and so long. Feel free to enhance it. I don't test for 9i and timestamp column in the log table.
Def TABLE = &1
Set echo OFF
Set feedback OFF
Set heading OFF
Set linesize 2000
Set long 32760
Set newpage 0
Set pagesize 0
Set recsep OFF
Set space 0
Set trimout ON
Set trimspool ON
BTitle OFF
TTitle OFF
Set serveroutput on size 100000 format wrap
Set termout off
Col tab noprint new_value TABLE
Col log noprint new_value TABLE_LOG
Col trg noprint new_value TABLE_TRG
Col seq noprint new_value TABLE_SEQ
Col v noprint new_value version
Select upper('&TABLE') tab,
substr('&TABLE',1,26)||'_LOG' log,
substr('&TABLE',1,23)||'_LOGTRG' trg,
substr('&TABLE',1,23)||'_LOGSEQ' seq, v
from (select to_number(substr (banner, instr (banner, 'Release') + 8, 1)) v
from v$version where banner like 'Oracle%');
Set termout on
Spool GenLog_&TABLE
Declare
/* Cursor for table columns */
type col_cursor is ref cursor;
c_col col_cursor;
rcol_cur user_tab_columns.column_name%type; rcol_old user_tab_columns.column_name%type; rcol_new user_tab_columns.column_name%type; rcol_type varchar2(255);
/* 8i cursor */
c_col8 varchar2(4000) :=
'Select col.column_name col_cur,
''OLD_''||substr(col.column_name,1,26) col_new,
''NEW_''||substr(col.column_name,1,26) col_old,
col.data_type||
decode(col.data_type,
''NUMBER'', decode(col.data_precision,
NULL, decode(col.data_scale, NULL, '''', ''(38)''),
''(''||col.data_precision||
decode(col.data_scale, NULL, '')'',
'',''||col.data_scale||'')'')),
''CHAR'', ''(''||col.data_length||'')'',
''VARCHAR2'', ''(''||col.data_length||'')'',
''NCHAR'', ''(''||col.data_length||'')'',
''NVARCHAR2'', ''(''||col.data_length||'')'',
''RAW'', ''(''||col.data_length||'')''
) col_type
from user_tab_columns col
where col.table_name = :tabname
order by col.column_id';
/* 9i cursor */
c_col9 varchar2(4000) :=
'Select col.column_name col,
''OLD_''||substr(col.column_name,1,26) col_new,
''NEW_''||substr(col.column_name,1,26) col_old,
col.data_type||
''NUMBER'', decode(col.data_precision,
NULL, decode(col.data_scale, NULL, '''', ''(38)''),
''(''||col.data_precision||
decode(col.data_scale, NULL, '')'',
'',''||col.data_scale||'')'')),
''CHAR'', ''(''||col.char_length||'' ''||
decode(col.char_used,''B'',''BYTE'',''CHAR'')||'')'',
''VARCHAR2'', ''(''||col.char_length||'' ''||
decode(col.char_used,''B'',''BYTE'',''CHAR'')||'')'',
''NCHAR'', ''(''||col.char_length||'' ''||
decode(col.char_used,''B'',''BYTE'',''CHAR'')||'')'',
''NVARCHAR2'', ''(''||col.char_length||'' ''||
decode(col.char_used,''B'',''BYTE'',''CHAR'')||'')'',
''RAW'', ''(''||col.data_length||'')''
) col_type
from user_tab_columns col
where col.table_name = :tabname
order by col.column_id';
c_colt varchar2(4000);
/* Table parameters */
tabname user_tables.table_name%type;
Begin
/* Initialisations */
tabname := '&TABLE';
if &version < 9 then c_colt := c_col8; else c_colt := c_col9; end if;
dbms_output.put_line (' ');
/* Log sequence creation */
dbms_output.put_line ('create sequence &TABLE_SEQ start with 0 increment by 1');
dbms_output.put_line (' minvalue 0 nomaxvalue nocycle cache 1000 order;');
dbms_output.put_line (' ');
/* Log table creation */
dbms_output.put_line ('create table &TABLE_LOG (');
dbms_output.put_line (' id number(38),');
if &version < 9 then
dbms_output.put_line (' time date,'); else
dbms_output.put_line (' time timestamp,');
end if;
dbms_output.put_line (' username varchar2(30),');
dbms_output.put_line (' action varchar2(30),');
open c_col for c_colt using tabname;
loop
fetch c_col into rcol_cur, rcol_old, rcol_new, rcol_type;
exit when c_col%NOTFOUND;
dbms_output.put_line (' '||rcol_old||' '||rcol_type||',');
dbms_output.put_line (' '||rcol_new||' '||rcol_type||',');
end loop;
close c_col;
dbms_output.put (' constraint '||substr('&TABLE',1,23)||'_LOG_PK');
dbms_output.put_line (' primary key (id));');
dbms_output.put_line (' ');
/* Trigger creation */
dbms_output.put_line ('create or replace trigger &TABLE_TRG');
dbms_output.put_line ('after insert or update or delete on &TABLE');
dbms_output.put_line ('for each row');
dbms_output.put_line ('begin');
dbms_output.put_line (' if inserting then');
dbms_output.put_line (' insert into &TABLE_LOG (');
dbms_output.put_line (' id, time, username, action');
open c_col for c_colt using tabname;
loop
fetch c_col into rcol_cur, rcol_old, rcol_new, rcol_type;
exit when c_col%NOTFOUND;
dbms_output.put_line (' ,'||rcol_new);
end loop;
close c_col;
dbms_output.put_line (' ) values ('); if &version < 9 then
dbms_output.put_line (' &TABLE_SEQ..nextval, sysdate, user, ''INSERT'''); else
dbms_output.put_line (' &TABLE_SEQ..nextval, systimestamp, user, ''INSERT''');
end if;
open c_col for c_colt using tabname;
loop
fetch c_col into rcol_cur, rcol_old, rcol_new, rcol_type;
exit when c_col%NOTFOUND;
dbms_output.put_line (' ,:new.'||rcol_cur);
end loop;
close c_col;
dbms_output.put_line (' );');
dbms_output.put_line (' elsif deleting then');
dbms_output.put_line (' insert into &TABLE_LOG (');
dbms_output.put_line (' id, time, username, action');
open c_col for c_colt using tabname;
loop
fetch c_col into rcol_cur, rcol_old, rcol_new, rcol_type;
exit when c_col%NOTFOUND;
dbms_output.put_line (' ,'||rcol_old);
end loop;
close c_col;
dbms_output.put_line (' ) values ('); if &version < 9 then
dbms_output.put_line (' &TABLE_SEQ..nextval, sysdate, user, ''DELETE'''); else
dbms_output.put_line (' &TABLE_SEQ..nextval, systimestamp, user, ''DELETE''');
end if;
open c_col for c_colt using tabname;
loop
fetch c_col into rcol_cur, rcol_old, rcol_new, rcol_type;
exit when c_col%NOTFOUND;
dbms_output.put_line (' ,:old.'||rcol_cur);
end loop;
close c_col;
dbms_output.put_line (' );');
dbms_output.put_line (' else /* updating */');
dbms_output.put_line (' insert into &TABLE_LOG (');
dbms_output.put_line (' id, time, username, action');
open c_col for c_colt using tabname;
loop
fetch c_col into rcol_cur, rcol_old, rcol_new, rcol_type;
exit when c_col%NOTFOUND;
dbms_output.put_line (' ,'||rcol_old);
dbms_output.put_line (' ,'||rcol_new);
end loop;
close c_col;
dbms_output.put_line (' ) values ('); if &version < 9 then
dbms_output.put_line (' &TABLE_SEQ..nextval, sysdate, user, ''UPDATE'''); else
dbms_output.put_line (' &TABLE_SEQ..nextval, systimestamp, user, ''UPDATE''');
end if;
open c_col for c_colt using tabname;
loop
fetch c_col into rcol_cur, rcol_old, rcol_new, rcol_type;
exit when c_col%NOTFOUND;
dbms_output.put_line (' ,:old.'||rcol_cur);
dbms_output.put_line (' ,:new.'||rcol_cur);
end loop;
close c_col;
dbms_output.put_line (' );');
dbms_output.put_line (' end if;');
dbms_output.put_line ('end;');
dbms_output.put_line ('/');
dbms_output.put_line (' ');
End;
/
Spool off
Regards
Michel Cadot
Received on Sat Mar 13 2004 - 11:06:59 CET