Re: Trigger on table with many fields

michaelg wrote:

> I have a problem using a trigger on a table with many field (>150).
> This is the trigger :
>
> create or replace trigger trg_tst
> after insert or update on tst_table1
> For each row
> declare lfd number(10);
> begin
> select test_seq.nextval into lfd from dual;
> insert into tst_table2
> values
> (:new.field1,
> :new.field2,
> :new.field3,
> .
> .
> :new.field150
> lfd);
> end:
>
> The field2 of table1 and table2 are the same, but table2 has added one
> field (seq_nr).
>
> The trigger works fine, but because of internal reasons in our system
> we save the trigger, when table1 is deleted and recreate it when table1
> gets created. But the table where it is saved, is quite short and it is
> not possible to change it, so the trigger body is cut offed. Do you
> have any ideas, how to optimize this trigger, to get it shorter ? I
> thought to call a function, which fills the field, but I am quite
> newbie in Oracle.

Like several others here, I'm baffled what you are atttempting to do. It seems that tst_table2 is just copy of tst_table1 with one added field. What's the point of that? Chances are you can accomplish the business requirement with some other approach (materialized view comes to mind).

You imply that tst_table1 gets deleted and recreated periodicaly - is tst_table2 some kind of archive for tst_table1? There are better ways to approach this kind of problem.

Does tst_table1 have a primary key? If not, your first mission should be to give it a PK. Then you could do this row copying without the field list, not that I'm advocating that you stick with that approach.

//Walt Received on Tue Sep 06 2005 - 11:08:34 CDT