Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    26

    Unanswered: Before update trigger

    Hi,
    I have written a trigger like below.

    CREATE TRIGGER RQ5.NON_SWIFT_TXN_CLNT_UPT AFTER UPDATE OF BUS_OR_ENTY_NM ON RQ5.NON_SWIFT_TXN_CLNT REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
    INSERT INTO RQ5.NON_SWIFT_TXN_INFO_HIST
    SELECT NON_SWIFT_ID, CURRENT TIMESTAMP, USER, 'P', EFTR_INCDNT_ID, SRC_SYS, PROC_DT, MSG_FLOW_TYP, REC_ID_NO, SENT_TM, TRNSMT_DT, TXN_AMT, CRNCY_CD, EXCH_RT,DR_CR_IND
    FROM RQ5.NON_SWIFT_TXN_INFO WHERE NON_SWIFT_ID = OLD.NON_SWIFT_ID
    ;--
    INSERT INTO RQ5.NON_SWIFT_TXN_CLNT_HIST
    SELECT NON_SWIFT_ID, CLNT_TYP, CURRENT TIMESTAMP, DOB, INDV_DOC_ID, INDV_OTH_NM, INDV_GIVEN_NM, INDV_SURNM, TEL_NO, OTH_ID_DESC, CLNT_NO, INDV_OCCTN, BUS_OR_ENTY_NM, CNTRY_CD, SUBCNTRY_CD, PSTL_ZIP_CD, CTY_NM, ST_ADDR, OTH_SUBCNTRY_NM, US_PSTCD_EXT, ST_ADDR_2, ST_ADDR_3, ST_ADDR_4, ACCT_NO, LOC_CD
    FROM RQ5.NON_SWIFT_TXN_CLNT
    WHERE NON_SWIFT_ID = OLD.NON_SWIFT_ID
    ;--
    END;

    But it inserts the record after updating the column. But my requirement is records need to be inserted in HIST table before updating the column in main table. I mean to say I need old values in HIST tables. In first insert there will be only one record for one NON_SWIFT_ID in main table, but in second insert there will be multiple records for one NON_SWIFT_ID in main table. All records belonging to same NON_SWIFT_ID should go to HIST table. Please let me know how to do it.

    Thanks
    Surjya

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    if you wanted a BEFORE trigger, you should have declared it as BEFORE, not AFTER.

    CREATE TRIGGER RQ5.NON_SWIFT_TXN_CLNT_UPT AFTER UPDATE OF...
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Jan 2010
    Posts
    26
    If I will use before update, then I can not intiate any operation on other table. Hence I can not use before update. I am looking for is there any other way.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Except doing this in the application, you can only use AFTER triggers to modify other tables. If you think about cascaded triggers, this makes a lot of sense, actually.

    Other question: An INSERT/UPDATE statement is an atomic operation. So why do you have to do the INSERT before the UPDATE? There is absolutely no need for it. You do have access to the old row in the trigger and can insert those old values into the history table.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2010
    Posts
    26
    Thanks stolze,
    I may be wrong, if I will use old as old then I will be able to insert only one record into hist table, not all records having same non_swift_id.
    Let say if will create trigger like below
    CREATE TRIGGER RQ5.NON_SWIFT_TXN_CLNT_UPT AFTER UPDATE OF BUS_OR_ENTY_NM ON RQ5.NON_SWIFT_TXN_CLNT REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
    INSERT INTO RQ5.NON_SWIFT_TXN_CLNT_HIST
    values( OLD.NON_SWIFT_ID, OLD.CLNT_TYP, CURRENT TIMESTAMP, OLD.DOB, OLD.INDV_DOC_ID, OLD.INDV_OTH_NM, OLD.INDV_GIVEN_NM, OLD.INDV_SURNM, OLD.TEL_NO)
    ;--
    END
    If there are four records having same non_swift_id, will it insert all four records into history table even though I update only one record in main table.
    Please provide some direction on it.

    Thanks
    Surjya

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is a FOR EACH ROW trigger. That means, the trigger fires for each row that is touched by the UPDATE operation. If there are rows with duplicated values and you update only one of those rows, the trigger fires only once. And the way you have written the trigger body implies that only a single row will be inserted into RQ5.NON_SWIFT_TXN_CLNT_HIST. If you want to copy all 4 rows, you'll need an INSERT INTO ... SELECT ... FROM ... WHERE ... to find all rows to be copied and insert them into the history table.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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