0

The trigger below copies all columns except col1 from main table to hist table once the date column is modified in main. I am planning to add a few more columns to main and hist , but every time I do this I would have to modify my trigger. Is there any way where I can make my trigger select all the columns present in the main except col1 ?

CREATE OR REPLACE TRIGGER ins_his BEFORE UPDATE OF datetime ON main
FOR EACH ROW
BEGIN

INSERT INTO hist (col2,col3) VALUES ( :new.col2, :new.col3 );

END;
Shailesh
  • 980
  • 8
  • 16
user3110283
  • 1
  • 1
    No; [related but not quite duplicate question](http://stackoverflow.com/q/9133120/266304). You could generate the trigger creation script from the table definition after each change, but it still has to be recreated somehow with the new columns listed explicitly. – Alex Poole Dec 19 '13 at 10:37

1 Answers1

0

I will give you an approach that could lead you to the solution. This code is really not tested because right now I don't have the time to do it, but I hope it can help you...

1) Create a cursor like this:

declare v_table_name      varchar2(30) := 'HIST'; 

cursor cur is select column_name, data_type 
              from all_tab_columns 
              where table_name = v_table_name 
              and column_name != 'COL1'
              order by column_id;

There you can get all the current columns of the table HINT that, of course, will take in consideration any alter table you do.

2) You must fetch that cursor and create a dynamic insert statement. Something like this:

declare v_column_list     varchar2(500);
declare v_values_list     varchar2(500);

for i in cur loop
    v_column_list := v_column_list||','||i.column_name;
    v_values_list := v_values_list||', :new.'||i.column_name;
end loop;

3) Create your dynamic insert statement and execute it:

declare csql                varchar2(500);

csql := 'INSERT INTO '||v_table_name||' ('||v_column_list||') VALUES ('||v_values_list||')';

EXECUTE IMMEDIATE (csql);

Hope this helps...

Javier
  • 2,093
  • 35
  • 50
  • Note that, even if it's possible to declare and execute dynamic statements in a trigger, it's usually a really bad idea, especially for something like a main/history table trigger: the overhead is simply too large/execution takes too long. You could _maybe_ optimize it by having it check if the table was `ALTER`ed, and storing the statement in an external table... but at that point you may as well just run the script to "permanently" re-gen the trigger. – Clockwork-Muse Dec 25 '13 at 03:46