Its me again =)
DB2V7 on OS390...

I created 2 triggers on the same table...
trigger1 will be triggered by an insert on the position table
If the FK/lodgement combination doesn't exist on the report table, a record will be inserted on the report table.
trigger2 will also be triggered by an insert on the position table
If the FK/lodgement combination exists on the report table, the report record will be updated..

trigger statements below...
--#SET TERMINATOR #
CREATE TRIGGER SFPUI.SECHOLD1
AFTER INSERT
ON SFPUI."SB_F_CUST_ACCT_POS"
REFERENCING NEW AS NEWVAL
FOR EACH ROW MODE DB2SQL
WHEN (0 =
(SELECT COUNT(*)
FROM SFPUI."SB_X_REPORT"
WHERE "FK_TSSB0454_CSE_01" = NEWVAL."FK_TSSB0454_CSE_01"
AND "C_LODGEMENT_CODE" = NEWVAL."C_LODGEMENT_CODE" ))
BEGIN ATOMIC
INSERT INTO SFPUI."SB_X_REPORT"
("FK_TSSB0454_CSE_01","C_LODGEMENT_CODE","N_TOTAL_ QUANTITY")
VALUES
(NEWVAL."FK_TSSB0454_CSE_01",NEWVAL."C_LODGEMENT_C ODE",
NEWVAL."N_TOTAL_QUANTITY")
;
END#

CREATE TRIGGER SFPUI.SECHOLD2
AFTER INSERT
ON SFPUI."SB_F_CUST_ACCT_POS"
REFERENCING NEW AS NEWVAL
FOR EACH ROW MODE DB2SQL
WHEN (1 <=
(SELECT COUNT(*)
FROM SFPUI."SB_X_REPORT"
WHERE "FK_TSSB0454_CSE_01" = NEWVAL."FK_TSSB0454_CSE_01"
AND "C_LODGEMENT_CODE" = NEWVAL."C_LODGEMENT_CODE" ))
BEGIN ATOMIC
UPDATE SFPUI."SB_X_REPORT"
SET "N_TOTAL_QUANTITY" =
"N_TOTAL_QUANTITY" + NEWVAL."N_TOTAL_QUANTITY"
WHERE "FK_TSSB0454_CSE_01" = NEWVAL."FK_TSSB0454_CSE_01"
AND "C_LODGEMENT_CODE" = NEWVAL."C_LODGEMENT_CODE"
;
END#

I inserted 2092 records on the position table.
Running the following query on the position and the report table shows that they are different when I thought the result should be the same...

position...
SELECT FK_TSSB0454_CSE_01, C_LODGEMENT_CODE, SUM("N_TOTAL_QUANTITY")
FROM SFPUI."SB_F_CUST_ACCT_POS"
GROUP BY FK_TSSB0454_CSE_01, C_LODGEMENT_CODE
ORDER BY 1,2;
result...
35650007560380. 109 716579.00000
35650007560380. 601 .00000
8277660009780013. 201 106287.00000
8277660009780013. 202 -1065.00000
9330710008400065. 601 3763.00000
9330710008400065. 851 1121767.00000
9516920008400067. 601 5500.00000
9516920008400067. 851 1788215.00000
9620040008400065. 851 851300.00000

report table..
SELECT *
FROM SFPUI."SB_X_REPORT"
ORDER BY 1,2;
result...
35650007560380. 109 716579.00000
35650007560380. 601 .00000
8277660009780013. 201 109287.00000
8277660009780013. 202 -2130.00000
9330710008400065. 601 7513.00000
9330710008400065. 851 1122310.00000
9516920008400067. 601 6500.00000
9516920008400067. 851 1788754.00000
9620040008400065. 851 852051.00000

The first 2 records of the result queries are correct but the other records are incorrect. Why????
Any help would be very much appreciated. =)

Thanks,
Paul

Both triggers will be fired after an insert.

The order of firing depends on the order you created them. If you created the "Insert"-trigger first, then the "Update"-trigger will fire on every row, since the first one creates a new row.

Thus, every row that didn't exist before the insert will be counted double in the report table..

Hope this makes sense
//Daniel