0

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.

Improve this question
8
  • Have you verified that your multi-row updates alter the value in the Stock_out column? Your insert is inside a conditional. Also, could you please provide a more specific post title? Thanks.
    – Elaskanator
    Commented Jul 25, 2018 at 16:42
  • 1
    Have you done a sanity check that the trigger is actually fired by the statement? SQL Server supports multi-row triggers so it definitely should. Outside of the conditional, log some stuff to a throwaway table (don't use temporary tables (the # notation), declare the table). Also run SQL Server Profiler at the same time to monitor what statements are going through the pipe (and include the Exception class). Also, consider if there is any rollback occurring. I swear I recall seeing you tag C# initially, so also be suspicious of error swallowing by your application.
    – Elaskanator
    Commented Jul 25, 2018 at 19:17
  • 1
    What @Elaskanator says. You could just comment out the SET NOCOUNT ON line and run an update in SSMS to see if the trigger generates its own "xxx row(s) affected" message as an indication whether it's actually doing its job.
    – Andriy M
    Commented Jul 25, 2018 at 19:19
  • 1
    FYI - for code readability, I recommend moving the i.Stock_out <> d.Stock_out check from the INNER JOIN...ON clause to the WHERE clause (not used at present). The columns aren't being used to determine which rows fit together, but rather which results should be output. With an INNER JOIN your result set should be the same either way, but it will help clarify the purpose of the different checks.
    – RDFozz
    Commented Jul 25, 2018 at 22:33
  • 1
    @RaheelAdam , Is there a trigger in Table B?
    – Biju jose
    Commented Jul 26, 2018 at 12:17

1 Answer 1

Reset to default
1

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.

Improve this answer
5
  • 2
    What issue do you see with UPDATE() for multiple rows? if the UPDATE statement affected a set of rows, it would affect the same columns for all of them. Even if it updates the column to the original value using some conditional logic, it is still considered an update. For example UPDATE T SET Col1 = CASE <something> ELSE Col1 END. There is no way for an UPDATE to affect only some columns for some rows, and other columns for other rows AFAIK.
    – SQLRaptor
    Commented Jul 25, 2018 at 17:16
  • 3
    i.Stock_out <> d.Stock_out is not redundant. The UPDATE() function only tells you whether the column has been written to by the triggering statement, and won't detect if the value happens to be same as before. Apparently the OP wants to log actual changes, so comparing the inserted and deleted versions makes perfect sense.
    – Andriy M
    Commented Jul 25, 2018 at 19:10
  • 1
    I figured as such, which is why also checking if the column was specified in the triggering statement is redundant (albeit presumably a small performance optimization). But it is crucial to verify that the value of the column is not being updated to NULL or updated from NULL.
    – Elaskanator
    Commented Jul 25, 2018 at 19:15
  • 1
    Yes, the point about null comparison is totally valid, although it might not be relevant for the OP's use case, because the column could be declared as non-nullable (which, I think you'll agree, would make sense for tables like the OP's)
    – Andriy M
    Commented Jul 25, 2018 at 19:22
  • I completely agree that the columns should be NOT NULL, but I completely disagree that it is reasonable to assume they are declared as such, as my real-world experience screams otherwise :(
    – Elaskanator
    Commented Jul 25, 2018 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.