Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    28

    Unanswered: Problem with triggers

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    24
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •