Re: Generic Insert Procedure to history tables

adimayuga_at_yahoo.com (Adrienne) wrote in news:ee790b43.0208061514.2ff67e31 @posting.google.com:

> Upon delete of a row, we are creating triggers to insert the row into
> a archive/history tables. What we could do is create a trigger for
> each table and each trigger will specify the archive table name,
> column names and values. To us it makes more sense to just create one
> common procedure for each trigger to call and send in the table
> name(since we will have a standard to name all archive tables
> <tablename>_ARCHIVE and the row values. The problem is that all
> tables have a different number of columns, and it makes more sense to
> just pass in the row and have the procedure insert the values in the
> same order. Is it possible to pass in an array of values or a cursor?
> I haven't seen any examples of this. Or is creating a trigger for
> each tablename our only way to implement this?
>
> Thanks in advance for any help.

As Daniel said, you really don't want to implement a dynamic single trigger, complexity will increase and reliability and performance will decrease. Code maintenance would in fact be a lot harder, individual cases harder to handle. Your initial typing and cut and pasting effort would be reduced though.

If this is the goal using the data dictionary to generate code is the best option, and it doesn't take reams of code to implement. I use a basic template like this and modify it as as needed by naming standards and required trigger type. This example builds after insert triggers with assumptions about the name and format of the archive table relative to its source table. I notice your archive tables follow a similar convention so you should have no problem modifying this approach to suit your situation. You could go wild and parameterize to make all the standards dynamic too, but I've found it takes less time to modify the generation script to suit.

The script gen_arc.sql is like this:

--
store set plus_set_save.sql replace 
set pagesize 0
set feedback off
set verify off
set trimspool on
set trimout on

undefine source_table

column trg_src new_value trg_src noprint
select lower('&&source_table') || '_arc_trg.sql' trg_src from dual
/
spool &trg_src

select
'create sequence ' || lower('&&source_table') || '_arc_key_seq' || chr
(10) ||
'    start with 1'                                       || chr(10) ||
'    increment by 1'                                     || chr(10) ||
'/'
from dual
/

set recsep off

select
'create or replace trigger ai_' || lower('&&source_table')  || chr(10) ||
'    after insert on ' || lower('&&source_table')           || chr(10) ||
'    for each row'                                          || chr(10) ||
'begin'                                                     || chr(10) ||
'    insert into ' || lower('&&source_table') || '_arc ('   || chr(10) ||
'        arc_key, '
from dual
/
select 
'        ' || lower(column_name) || ','
from user_tab_columns where table_name = upper('&&source_table')
/
select
'        add_date)'     || chr(10) ||
'    values ('          || chr(10) ||
'        ' || lower('&&source_table') || '_arc_key_seq.nextval,'
from dual
/
select 
'        :new.' || lower(column_name) || ','
from user_tab_columns where table_name = upper('&&source_table')
/
select 
'        sysdate);'     || chr(10) ||
'end;'                  || chr(10) ||
'/'                     || chr(10) ||
''                      || chr(10) ||
'show errors'
from dual
/
spool off
@plus_set_save.sql
@&trg_src

--

Here is an example of it in use, once the tables have been created you 
just run it in SQL*plus using @gen_arc and input the source table name 
when prompted:

SQL> create table t1 (
  2  col_a varchar2(20),
  3  col_b varchar2(20))
  4  /

Table created.

SQL> edi
Wrote file afiedt.buf

  1  create table t1_arc (
  2  arc_key number,
  3  col_a varchar2(20),
  4  col_b varchar2(20),
  5* add_date date)
SQL> /

Table created.

SQL> edi
Wrote file afiedt.buf

  1  create table t2 (
  2  col_a varchar2(20),
  3  col_b varchar2(20),
  4* col_c varchar2(20))
SQL> /

Table created.

SQL> edi
Wrote file afiedt.buf

  1  create table t2_arc (
  2  arc_key number,
  3  col_a varchar2(20),
  4  col_b varchar2(20),
  5  col_c varchar2(20),
  6* add_date date)
SQL> /

Table created.

SQL> @gen_arc
Wrote file plus_set_save.sql
Enter value for source_table: t1

create sequence t1_arc_key_seq
    start with 1
    increment by 1
/

create or replace trigger ai_t1
    after insert on t1
    for each row
begin
    insert into t1_arc (
        arc_key,
        col_a,
        col_b,
        add_date)
    values (
        t1_arc_key_seq.nextval,
        :new.col_a,
        :new.col_b,
        sysdate);
end;
/

show errors

Sequence created.

Trigger created.

No errors.
SQL> @gen_arc
Wrote file plus_set_save.sql
Enter value for source_table: t2

create sequence t2_arc_key_seq
    start with 1
    increment by 1
/

create or replace trigger ai_t2
    after insert on t2
    for each row
begin
    insert into t2_arc (
        arc_key,
        col_a,
        col_b,
        col_c,
        add_date)
    values (
        t2_arc_key_seq.nextval,
        :new.col_a,
        :new.col_b,
        :new.col_c,
        sysdate);
end;
/

show errors

Sequence created.

Trigger created.

No errors.

SQL> insert into t1 values ('a','b');

1 row created.

SQL> select * from t1_arc;

 ARC_KEY COL_A        COL_B        ADD_DATE
-------- ------------ ------------ ---------
       1 a            b            06-AUG-02

SQL> insert into t2 values ('a','b','c');

1 row created.

SQL> select * from t2_arc;

 ARC_KEY COL_A        COL_B        COL_C        ADD_DATE
-------- ------------ ------------ ------------ ---------
       1 a            b            c            06-AUG-02


It also generates the required source (<table_name>_arc_trg.sql) so 
manual tweaking is always possible for those *unusual* exceptions that 
don't match the generic business rules.

hth

Martin Burbridge