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

Have a look at this thread ..

Update Statment with complex Where clause

Does this help ?

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

HTH

Sathyaram

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

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

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

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