Trigger not updating a field with a null value

[AnthonyWhitehouse]

Greetings - I have a sql trigger that sets the value on a field called SOH in the products table whenever the quantity for location 1 is updated.
This works - but sometimes it doesnt work. Investigating I think this is when the existing value of SOH is null.
I can not understand why this is - or how to resolve it - can someone please help - thanks.
My trigger is as follows
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_update_loc_quantity]
ON [dbo].[PLOCDETAILS] AFTER UPDATE
AS
IF UPDATE(quantityonhand)
BEGIN
update dbo.PRODUCTS
set dbo.Products.SOH = dbo.PLOCDETAILS.QuantityOnHand
from dbo.PRODUCTS
inner join dbo.PLOCDETAILS on dbo.PRODUCTS.UniqueID = dbo.PLOCDETAILS.ProductID Where PRODUCTS.uniqueid=(Select productid from inserted) and PLOCDETAILS.LocationNo = 1
END

 

[gmmastros]

There is a bigger problem here.

When you write a trigger, the first thing you should think about is.... This must accommodate multiple rows getting updated. The last thing you need to think about is... does this accommodate multiple rows getting updated.

Unfortunately, your trigger code does not accommodate multi-row updates. This part of your code is the problem.

[tt]PRODUCTS.uniqueid=[!]([/!]Select productid from inserted[!])[/!] [/tt]

The parenthesis indicates that you are looking for a single value from that statement. If you issue an update command that affects multiple rows, the inserted table will have multiple rows, and your entire query will fail.

ALTER TRIGGER [dbo].[trg_update_loc_quantity] 
ON [dbo].[PLOCDETAILS] AFTER UPDATE
AS
IF UPDATE(quantityonhand)
  BEGIN
    update dbo.PRODUCTS
    set    dbo.Products.SOH = dbo.PLOCDETAILS.QuantityOnHand
    from   dbo.PRODUCTS 
           inner join dbo.PLOCDETAILS 
             on dbo.PRODUCTS.UniqueID = dbo.PLOCDETAILS.ProductID 
           inner join inserted As I
             on PRODUCTS.uniqueid = I.productid
    Where  PLOCDETAILS.LocationNo = 1
END

Given this mistake, I would encourage you to check all the triggers you have in your database to see if the same problem occurs elsewhere. If you are not sure what triggers you have, you can run this query.

Select * From sys.triggers

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AnthonyWhitehouse]

Ahhhh - I think normally the database would be updating the product quantity in just one location ... but perhaps there is more than one when they do a Stock Transfer between locations?
Is there anyway that I could test to see if there are multiple entries in Inserted?
How would you suggest that it is best to alter my trigger to allow for them?
Thanks Anthony

 

[gmmastros]

How would you suggest that it is best to alter my trigger to allow for them?

I would suggest you try the code I posted earlier. It should accommodate multiple row updates. Make sure you test this really well.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AnthonyWhitehouse]

Is there anyway that I could test to see if there are multiple entries in Inserted?

 

[gmmastros]

The following query will add 0 to every quantity on hand value. Adding zero to any number will return the same number. However, SQL Server will still go through the motions of updating the quantity on hand column for every row eventhough it won't actually change the value.

UPDATE PLOCDETAILS
SET    QuantityOnHand = QuantityOnHand + 0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AnthonyWhitehouse]

Thanks gmmastros - I have modified my trigger to use your suggested join but I still have the same problem.
A clarification - I am actually trying to update two fields in the products table whenever the plocdetails quantity changes.
I am updating products.zsohakl with the quantity in plocdetails for location = 1 and products.zsohhold for location = 10
My trigger sometimes updates both fields... but sometimes only zsohakl and leaves zsohhold untouched.
On Further Investigation, I think this is when there is no existing record in Plocdetails for location 10

For example - a product has stock only in location 1, so has never created a plocdetails record for any other locations.
I do a transfer from location 1 to location 10 which will create a record in plocdetails for location 10 - but doesnt update products.zsohhold
I do a second transfer and zsohhold is updated, as a record does now exist.

After your suggested change, my trigger is now as follows..
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_update_loc_quantity]
ON [dbo].[PLOCDETAILS] AFTER UPDATE
AS
IF UPDATE(quantityonhand)
BEGIN
update dbo.PRODUCTS
set dbo.Products.zSOHAKL = dbo.PLOCDETAILS.QuantityOnHand
from ((dbo.PRODUCTS
inner join dbo.PLOCDETAILS on dbo.PRODUCTS.UniqueID = dbo.PLOCDETAILS.ProductID) inner join Inserted on PRODUCTS.uniqueid = inserted.productid) where PLOCDETAILS.LocationNo = 1
update dbo.PRODUCTS
set dbo.Products.zSOHHold = dbo.PLOCDETAILS.QuantityOnHand
from dbo.PRODUCTS
inner join dbo.PLOCDETAILS on dbo.PRODUCTS.UniqueID = dbo.PLOCDETAILS.ProductID) inner join Inserted on PRODUCTS.uniqueid = inserted.productid) where PLOCDETAILS.LocationNo = 10
END

 

[gmmastros]

I do a transfer from location 1 to location 10 which will [!]create a record[/!] in plocdetails for location 10 - but doesnt update products.zsohhold

ALTER TRIGGER [dbo].[trg_update_loc_quantity] 
ON [dbo].[PLOCDETAILS] [!]AFTER UPDATE[/!]

If you insert a record, this trigger will not fire because it is an update trigger.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom