I have table_a and table_b, below is the data from 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.