Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    15

    Unanswered: cursor help needed

    I need a trigger that updates a second table but the
    trigger below seems to update all the rows not the one
    only.

    update on_hand set on_hand.qty = on_hand.qty - ( Select a.qty from adjust a, on_hand o where a.part = o.part);

    ( I was told a cursor would help with this problem)

    table structure
    table ADJUST
    ID INTEGER
    PART CHAR
    QTY INTEGER

    TABLE ON_HAND
    ID INTEGER
    PART CHAR
    QTY INTEGER

    GOAL : substact ( qty on_hand - qty adjust )

    Adjust only has 1 row of data.
    on_hand has many unique rows.

    example data: from on_hand

    1----a1000-----100
    2----a2000-----100
    3----a3000-----100

    Any ideas/thanks
    BJ

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have a look at this thread ..

    http://www.dbforums.com/showthread.php?t=1208514

    Does this help ?

    BTW, you have mentioned 'trigger' . Can you post the trigger code

    HTH

    Sathyaram

    Quote Originally Posted by bobjohnson360
    I need a trigger that updates a second table but the
    trigger below seems to update all the rows not the one
    only.

    update on_hand set on_hand.qty = on_hand.qty - ( Select a.qty from adjust a, on_hand o where a.part = o.part);

    ( I was told a cursor would help with this problem)

    table structure
    table ADJUST
    ID INTEGER
    PART CHAR
    QTY INTEGER

    TABLE ON_HAND
    ID INTEGER
    PART CHAR
    QTY INTEGER

    GOAL : substact ( qty on_hand - qty adjust )

    Adjust only has 1 row of data.
    on_hand has many unique rows.

    example data: from on_hand

    1----a1000-----100
    2----a2000-----100
    3----a3000-----100

    Any ideas/thanks
    BJ
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2006
    Posts
    15

    cursor help

    Here is my trigger:

    CREATE TRIGGER BOB.ADJUST4 AFTER INSERT ON BOB.ADJUST FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    update on_hand set on_hand.qty = on_hand.qty - ( Select a.qty from adjust a, on_hand o where a.part = o.part);
    END

    In the mean time I will look at the link you supplied
    and see if that shades some light on my issue.
    ------------Thanks ----------
    BJ

  4. #4
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    You need a where clause in your trigger to specify which row(s) in the on_hand table you want to update, ie the row that corresponds to the ADJUST item that the trigger is firing for.
    It will probably be similar to the where clause you are using to select the qty in the subselect you have.


    Something like:
    CREATE TRIGGER BOB.ADJUST4 AFTER INSERT ON BOB.ADJUST FOR EACH ROW
    REFERENCING NEW as INSERTED
    MODE DB2SQL
    BEGIN ATOMIC
    update on_hand set on_hand.qty = on_hand.qty - ( Select a.qty from adjust a, on_hand o where a.part = o.part)
    WHERE INSERTED.part = on_hand.part
    END

  5. #5
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Also, using the REFERENCING clause to give the update row a name, you can lose the subselect you are doing.

    ex:
    update on_hand set on_hand.qty = on_hand.qty - INSERTED.qty
    WHERE INSERTED.part = on_hand.part

  6. #6
    Join Date
    Jan 2006
    Posts
    15

    trigger update code

    craigmc


    I ran the code you supplied in pane # 4
    including the referencing statement and it seems to
    function perfectly !!
    I have been looking for this solution for some time.
    -----------Thanks very much -------------
    BJ

Posting Permissions

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