Trigger design issues

[Michel Esber]

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,

[Serge Rielau]

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

begin atomic
delete from TABLE_B where (ID, TIMESTAMP) IN (SELECT ID, timestamp
FROM n);
insert into TABLE_B SELECT Id, TIMESTAMP, VALUE FROM n;
end@

Here is what may be a faster solution:
CREATE TRIGGER TRG.INS_IOSTAT
AFTER INSERT ON TABLE_A
REFERENCING
NEW TABLE AS n
FOR EACH STATEMENT
MODE DB2SQL
MERGE INTO TABLE_B AS T USING N ON N.ID = T.ID
WHEN MATCHED THEN UPDATE SET (TIMESTAMP, VALUE)
= (N.TIMESTAMP, N.VALUE)
WHEN NOT MATCHED THEN INSERT VALUES(ID, TIMESTAMP, VALUE)
@
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[mirof007]

Have you looked a the samples in $DB2INSTHOME/sqllib/samples? In any
case, I think the following should work:

CREATE TRIGGER TRG.INS_IOSTAT
AFTER INSERT ON TABLE_A
REFERENCING NEW_TABLE AS N
FOR EACH STATEMENT
MODE DB2SQL

BEGIN ATOMIC
DELETE FROM TABLE_B
WHERE TABLE_B.ID IN (SELECT ID FROM N);
INSERT INTO TABLE_B SELECT ID, TIMESTAMP, VALUE FROM N;
END@

Regards,
Miro

[Michel Esber]

Thanks guys.

I will test all solutions.