Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Unanswered: db2 Update Trigger help?

    I'm using DB2 9.7 with AIX 6.1. I'm new to Triggers.
    I'm working on an Update trigger that if the first Table updates rows it would automatically update the second Table with the same updates.

    Here is what I have so far (Update SLDCTO field) and it updates the first table (F42199) but not the second table(F42199_PART):

    db2 "create trigger DB2INST1.F42199_PART_UT after update on PRODDTA.F42199 REFERENCING old AS O FOR EACH ROW MODE DB2SQL
    update PRODDTA.F42199_PART AS N
    set N.SLDCTO=O.SLDCTO
    WHERE O.SLKCOO=N.SLKCOO and O.SLDOCO=N.SLDOCO and O.SLDCTO=N.SLDCTO and O.SLLNID=N.SLLNID"

    I do appreciate your help on this because I am not getting this to work and really need an experieced person to assist me on what is wrong.

    Thank you so much.

    CC

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    My guess would be that there is no record "WHERE O.SLKCOO=N.SLKCOO and O.SLDOCO=N.SLDOCO and O.SLDCTO=N.SLDCTO and O.SLLNID=N.SLLNID"
    Last edited by n_i; 01-14-13 at 15:23.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Actually, that's not necessarily the case. Notice that you "set N.SLDCTO=O.SLDCTO" where "... O.SLDCTO=N.SLDCTO...", so if there _is_ such a record, you wouldn't know if it was changed, as you set the column to the same value as before. No?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2007
    Posts
    84
    The Where clause with this: WHERE O.SLKCOO=N.SLKCOO and O.SLDOCO=N.SLDOCO and O.SLDCTO=N.SLDCTO and O.SLLNID=N.SLLNID
    is only joining these two tables to their primary keys.

    I took that out completely in the Trigger and it still doesn't update the F42199_PART table when I do an Update.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rockycj View Post

    I took that out completely in the Trigger and it still doesn't update the F42199_PART table when I do an Update.
    Prove it by posting:

    - relevant select statements before an update;
    - the actual update statement;
    - similar select statements after the update;
    - the actual trigger text.

    Copy/paste the output.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Feb 2007
    Posts
    84
    Got the Update Trigger running properly now. Here is what I changed it to:

    db2 "create trigger DB2INST1.F42199_PART_UT after update on PRODDTA.F42199
    REFERENCING old AS O new as N FOR EACH ROW MODE DB2SQL
    update PRODDTA.F42199_PART
    set SLDCTO=N.SLDCTO
    where SLKCOO=O.SLKCOO and SLDOCO=O.SLDOCO and SLDCTO=O.SLDCTO and SLLNID=O.SLLNID"

    So with my Update statement of this (SLDCTO equals S4 before update):

    update proddta.F42199
    set SLDCTO = 'S5'
    WHERE SLKCOO = '00010'
    AND SLDOCO = '02311902'
    and SLDCTO = 'S4'

    Then both F42199 and F42199_PART tables the SLDCTO field changes to S5.
    That is what I'm looking for.

    Thanks.

    CC

Posting Permissions

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