Hi everyone,
I am new to triggers. I have created a FOR EACH STATEMENT update trigger. This is for DB2 iSeries V6R1 database.
I need to iterate over the NEW_TABLE transition table and perform some tasks. Looks like I can't use the DECLARE csr CURSOR FOR statement in the trigger. I also tried using the FOR v1 AS c1 CURSOR FOR.... statement and I am noticing some weird behavior. It seems to work sometimes. Both these constructs are not in the list of valid clauses I can use in a trigger according to the CREATE TRIGGER documentation. So my question is, how can I iterate over the NEW_TABLE or OLD_TABLE transition tables?

Thanks for your help!
Henok
P.S. I have have pasted my sample below. See the sections labeled Example 1 and Example 2.

CREATE TRIGGER update_pack_unpack_item_tr
AFTER UPDATE on pack_unpack_item
REFERENCING OLD_TABLE AS otable NEW_TABLE AS ntable
FOR EACH STATEMENT MODE DB2SQL

p1: BEGIN ATOMIC

DECLARE no_data SMALLINT DEFAULT 0;

-----------Example 1------------
DECLARE csr CURSOR FOR
SELECT pack_unpack_item_id, item_id
FROM ntable
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_data = 1;

OPEN csr;
WHILE no_data = 0
DO
FETCH csr INTO p1.n_pack_unpack_item_id, p1.n_item_id;
--Do some work in here with current row
END WHILE;
CLOSE csr;

---------Example 2-----------
for_1:FOR v1 AS c1 CURSOR FOR
SELECT pack_unpack_item_id, item_id
FROM otable
DO
--Do some work in here with current row
END FOR for_1;

END;

what is it you are wanting this trigger to do? I really can't understand why you would be iterating through the table when a row getts updated.

Dave

This trigger needs to generate a report of the fields that were modified by an update SQL statement. I need build this change summary and store it in a table. This is the client's requirement. Then a user will go on some web page and view this change history.

So here's what I need to do in this trigger:

*Iterate over NEW_TABLE
*For each row in new table, fetch corresponding row in OLD_TABLE
*Do a field-by-field comparison of these two rows and log the changes to my history table.

I am using a FOR EACH STATEMENT trigger because I need to be able to group the updates made by one sql query together. That way, if the sql update modified ten rows, I treat that group of modifications as one group, in my history table.

Thanks,

Henok

You can use a FOR loop in the trigger body and a query to iterate over the transition table(s).

Have a look at "Example 3" in the manual: http://publib.boulder.ibm.com/infoce.../r0000931.html

There is no need to iterate through the table, though. Its just do something with the updated rows. The easiest way to do this would be to copy the entire contents of each row to your history table. Otherwise, it gets very difficult only putting out what has changed, because you would have to put the column name and value out to your history table and how do you accomodate all data types in the history table, also, how do you take care of multiple columns being updated?
The easiest way I have seen it done in the past is to create a history table that matches your current table add a column to this history table that tracks whether it is old/updated row. Here is a quick overview of the statement.

Dave Nance

Thank you all for the replies,

stolze, the link you sent me is for DB2 Version 9.5 for Linux, UNIX, and Windows. I have DB2 for iSeries 6.1. I couldn't find the CREATE TRIGGER documentation for iSeries 6.1 but I found it for iSeries 5.4 and they don't have the FOR LOOP in the list of allowed statements in a trigger. But, someone else just told me that he was able to use a FOR loop on their DB2 iSeries 6.1. So he suggested that I make sure we have all the latest PTFs installed and try again. I am going to do that. It turns out we are behind on PTFs on that machine.

dav1mo, your idea is a good one. However we have many tables to audit (about 30) and it means we would need to create one history table for each table we are auditing. That's why we have decided to create one generic history table that just stores strings (TABLE_NAME, FIELD_NAME, OLD_VALUE, NEW_VALUE...ETC.) The client also wants summarized and formatted descriptions of changes...so just dumping transition table into history table as is won't work. Thank you for the suggestion!

Thank you all! I will update you as soon as I find out if having the latest PTFs takes care of my problem.

Here is another suggestion:

• you copy the changes as-is to a staging table as dave suggested
• then you run a hourly/daily/weekly/whenever job to do the describe aggregation and compaction of the data to reduce the data volume

An advantage would be that the triggers are much simpler and, thus, reduce the performance impact to DML statements because you don't have to do the aggregation in each statement and can delay it. But you will still reduce the volume of data.

I meant to iterate over the transition tables, which contain only the new/old updated rows - not the whole tables.

Knut, I think that's a good idea. I have already noticed some delay in my DML operations when my trigger does a lot of work. So I will need to simplify the trigger and move the bulk of the processing to a scheduled job.

Thanks!