About the trigger of DB2

greenspun.com : LUSENET : DBAzine : One Thread

I need to create some dynamic tables. There is a column that store the updating timestamp in the table. I want to create a trigger that will update the timestamp column when any field of a record in this table is modified. But in DB2, I have to write only one column for update, but I don't want to create many triggers in one table. How can I do for resolving it?

-- Fairy (fli@objectivasoftware.com), July 23, 2002

Answers

All you need to do is create an AFTER UPDATE FOR EACH ROW trigger on the table in question and use transition variables. In the trigger body (the code) you will need an UPDATE statement something like this:

UPDATE TABLE SET TIMESTAMP_COL = CURRENT TIMESTAMP WHERE KEY = NEW.KEY

The WHERE clause will match the key of the table with the key of each row that was updated. Since it is a FOR EACH ROW trigger, the trigger will not be executed if no rows are impacted by an UPDATE statement... however, for every row that is impacted it will update the timestamp to the current timestamp.

Good luck... Craig

-- Craig S. Mullins (craig_mullins@bmc.com), July 24, 2002.


Moderation questions? read the FAQ