Generate SQL scripts for triggers

Question

I often have to create trigger which will copy all contents of the rows on insert/update to another table. As some tables have 200 columns this often is a long writing of

CREATE TRIGGER scheme.trigger AFTER UPDATE ON scheme.table
  REFERENCING OLD as o_row NEW as n_row
  FOR EACH ROW
BEGIN
  INSERT INTO archive (...) VALUES(...);
END;

This is a lot of typing. Is there an easy generator to build these type of triggers, inserts, updates?

I haven't done DB2 in years, but isn't there a data dictionary table you can reference to get the column names? Write a stored proc to output the above, while inserting into that output the list of columns? I have done that in Oracle and SQL Server, so I assume that is how you would do it in DB2. — MJB
– MJB, Commented May 10, 2011 at 12:20
I am planning to do an assistant tool for that but I am a bit short on time currently and was wondering if there is no freeware tool available doing this. — ZoolWay
– ZoolWay, Commented May 10, 2011 at 12:38
start with "select * from systables" and go from there. I suspect it is far easier to roll your own than to search for the exact tool. this is not that difficult a problem. — MJB
– MJB, Commented May 10, 2011 at 13:59

AngocA · Accepted Answer · 2011-05-12 14:21:03Z

1

If the process has a set of input parameters and the process with these parameters is the same, you can call a stored procedure from the trigger, passing the parameters. In this way, you do not have to recopy/recreate all these DML sentences.

answered May 12, 2011 at 14:21

AngocA

7,7046 gold badges42 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ZoolWay Over a year ago

This sounds quite good but the count of parameters differs from table to table. Are variable parameter lengths supported by iSeries DB2 stored procedures (maybe you know)?

ZoolWay Over a year ago

Accepted as there is no tool and this is the most flexible way to handle it manually.

Fred Sobotka · Accepted Answer · 2011-05-10 15:34:11Z

1

I don't know of a tool that generates the CREATE TRIGGER statements for you, but I've generated that DDL from a query many times, and the only view I ever needed to references was SYSCAT.COLUMNS

The following factors will simplify the trigger DDL generator query you'll be writing: - Column names between original table and archive table are identical. - None of the columns in the target table are defined as GENERATED ALWAYS, otherwise you'll need to omit them from your INSERT statement.

answered May 10, 2011 at 15:34

Fred Sobotka

5,33225 silver badges34 bronze badges

Collectives™ on Stack Overflow

Generate SQL scripts for triggers

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related