Hello,

LUW V8 FP 12.

My application issues INSERT statements into a table:

INSERT INTO TABLE_A values (ID, TIMESTAMP, VALUE1), (ID, TIMESTAMP,
VALUE2), (ID, TIMESTAMP, VALUE3) ...

There is an AFTER INSERT trigger on TABLE_A that inserts all rows from
the last inserted (ID, TIMESTAMP) into another table.

Here is the code:

CREATE TRIGGER TRG.INS_IOSTAT
 AFTER INSERT ON TABLE_A
 REFERENCING
   NEW AS n
 FOR EACH ROW
 MODE DB2SQL

 begin atomic
 delete from TABLE_B where ID=n.ID and TIMESTAMP!=n.timestamp;
 insert into TABLE_B values (n.Id, n.TIMESTAMP, n.VALUE);
 end@

TABLE_A keeps all inserted rows, and TABLE_B is meant to keep only the
last TIMESTAMP inserted in TABLE_A.

I know this solution is fairly poor. FOR EACH ROW, a delete statement
is used to remove old data from TABLE_B. I eventually see lock-waits on
this table.

I´ve read the DOCs and I understand that a better solution is to use
FOR EACH STATEMENT triggers referencing all rows with NEW_TABLE.
But there are no samples on how to use NEW_TABLE inside the trigger
code. Do I need to open a cursor to fetch its rows?

Is there a simple way to change this "for each row" trigger to use a
"for each statement" ? Any other ideas?

Thanks in advance,