September 23, 2003 at 11:15 am
The first UPDATE statement in the trigger below works, but not the second. I verified there is data for the second UPDATE, but it seems like it's not getting fired. The second UPDATE uses slightly different linking to find a different record than is used in the first UDPATE.
Any suggestion as to where I've screwed up ?
Thanx much,
Beth L
CREATE TRIGGER AddXfrLine
ON dbo.OELINXFR
AFTER INSERT
AS
UPDATE IM_SUBITEM
SET QtyOnHand = QtyOnHand + ins.qty_to_ship
FROM inserted ins INNER JOIN IM_SUBITEM sub
ON ins.item_no = sub.Item_no and ins.user_def_fld_3 = sub.Loc and ins.user_def_fld_4 = sub.SubLoc
UPDATE IM_SUBITEM
SET QtyOnHand = QtyOnHand - ins.qty_to_ship
FROM inserted ins INNER JOIN IM_SUBITEM sub
ON ins.item_no = sub.Item_no and ins.Loc = sub.Loc and ins.user_def_fld_5 = sub.SubLoc
September 23, 2003 at 12:53 pm
Hi Beth.
This certainly seems strange.
Have you tried reversing the order of the UPDATE statements within the trigger to see what happens?
Another idea would be to split these into separate triggers and see what happens.
Good luck.
- Mike
September 23, 2003 at 1:57 pm
It seems that no matter which order the two UPDATE statements are in, the first one is the only one that fires. Am I missing some key word to force it to run the second UPDATE?
I tried moving one of the UDPATE statements to a new trigger, but then neither of them fired...
And yes, I do still have some hair left, even if it's more gray than when I got in this morning...
Beth L
September 23, 2003 at 2:58 pm
Hi again.
Is there any chance that IM_SUBITEM has an UPDATE trigger itself that updates the OLEINXFR table?
That would mean a recursive trigger exists and could explain what is happening.
- Mike
September 23, 2003 at 7:11 pm
Nope, there is no trigger on the IM_SUBITEM table. There has got to be a way to have it continue on to the second UPDATE statement - I'm just not finding it. I've looked in BOL and the four hardcopy books I've got around to no avail.
Any other suggestions ??
Beth L
September 24, 2003 at 1:26 am
I think you are missing out on a "Begin" and "End".
September 24, 2003 at 7:55 am
I tried that and it still didn't help the problem.
It does whichever UPDATE comes first in the trigger successfully (I've swapped the two around and know it's not the syntax of the second UPDATE). I'm wondering if the inserted table is emptied after the first UPDATE runs. Could be there is no data left in it for the second UPDATE to run.
Is there some way I can test for this possibility?
Beth L
September 24, 2003 at 8:08 am
To test this, just insert the values from the INSERTED table into another table.
Do this before and after the first query. Maybe you will see what is happening than.
September 24, 2003 at 8:14 am
Tried it, and the inserted table is NOT cleared, as I was expecting. Has to be something else.
Maybe try substituting the join conditions to make sure there is a record matching to troubleshoot the query itself...
September 24, 2003 at 9:02 am
I finally got it to work!
Thanx netmikem & NPeeters - it turned out to be a combo of both suggestions.
I wrote inserted to another table and found one of the fields I was using in the joins was NULL.
One of my other triggers wasn't updating that field early enough.
Here's the final working Trigger:
CREATE TRIGGER AddXfrLine
ON dbo.OELINXFR
AFTER INSERT
AS
UPDATE IM_SUBITEM
SET QtyOnHand = sub.QtyOnHand - ins.qty_to_ship
FROM inserted ins INNER JOIN IM_SUBITEM sub
ON ins.item_no = sub.Item_no AND ins.Loc = sub.Loc AND ins.user_def_fld_5 = sub.SubLoc
UPDATE IM_SUBITEM
SET QtyOnHand = sub.QtyOnHand + ins.qty_to_ship
FROM inserted ins INNER JOIN IM_SUBITEM sub
ON ins.item_no = sub.Item_no and ins.user_def_fld_3 = sub.Loc and ins.user_def_fld_4 = sub.SubLoc
Not much different than the first one I tried.
Thanx again !
Beth L
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply