Sql-server – Update trigger seemingly won’t fire if multiple rows are updated

sql serversql server 2014

I have table_a and table_b, below is the data from table_a.

table_a

Bill_No   P_Name  Price   Stock_in  Stock_out  Stock_in_hand  P_Value
1         fish     100       20        10            10          1000
2         water    50        40        30            10          500
3         soda     30        50        10            40          1200 
4         cake     5         10        10             0          0

Now whenever Stock_out gets updated I want to insert those updated rows into table_b via a TRIGGER. Bill_No is identity column and primary key here.
This is what I have tried so far. 

ALTER TRIGGER [dbo].[copytblab] 
   ON  [dbo].[table_a]
   AFTER UPDATE
AS 
IF (UPDATE(Stock_out))
BEGIN
  SET NOCOUNT ON;
  INSERT table_b(Bill_No,P_Name,Price,Stock_in,Stock_out,Stock_in_hand, P_Value)
    SELECT i.Bill_No,i.P_Name,i.Price,i.Stock_in,i.Stock_out,i.Stock_in_hand,i.P_Value FROM inserted AS i
    INNER JOIN deleted AS d
    ON i.Bill_No = d.Bill_No
    AND i.Stock_out <> d.Stock_out
END

The code works fine if I update a single row but If i update more than one row at one time then it does not send Stock_out, P_Value and Stock_in_hand values to table_b. I don't understand where the problem is.

Best Answer

Your INSERT is nested under IF UPDATE(Stock_out) but then filters on i.Stock_out <> d.Stock_out which is redundant.

If Stock_out is ever allowed to be NULL then your WHERE clause will never match (because NULL IS NULL evaluates as true but NULL <> NULL always evaluates as NULL which will not match). Please check that.

I am unsure how the UPDATE() function responds to multi-row invocations as the Microsoft Docs don't seem to mention it! Somebody please update me on this. I would avoid using something with unclear behavior.

Related Question