Why is my SQL stored procedure not updating a specific value in the table?-CodePudding

I have a stored procedure containing the following code (simplified and genericised):

UPDATE t
SET t.intFoo = @foo,
    t.intBar = @bar,
    t.intBaz = tst.intBaz
FROM dbo.myTable t,
     dbo.myOtherTable tst
WHERE (t.id = @myId)
AND (tst.alias = 'Alias')

@foo is generated earlier on in the stored procedure, while @bar is passed in as an int parameter. intBar is known to be null on every row in the table.

After the stored procedure is called, when I check the updated row in the database, I see that intFoo and intBaz have been set correctly, but intBar is still set to null. This happens even if I explicitly hardcode a value for intBar to be set to:

UPDATE t
SET t.intFoo = 12345, --this gets stored in the targeted row
    t.intBar = 54321, --this does not; it remains null
    ...

intBar is a completely ordinary column, with no constraints or anything that would block it from being set:

...
[intBar] [int] NULL

I have run the stored procedure in isolation, and confirmed that a) @bar is being passed in correctly and is not null, and b) the value of intBar is not being overwritten by another stored procedure. I've tried running just the UPDATE statement in isolation, and again, intBar is not updated while every other value is.

What's going on here? Why is my stored procedure updating some of the values I'm asking it to, but not all of them?

CodePudding user response：

After closer inspection, it turns out there was a trigger on the table that was controlling the updates. (I couldn't find it initially because this is extremely messy legacy code and nothing is where you expect it to be.) intBar was only recently added to the table, and must not have been added to the trigger at that time, therefore it wasn't being included in the update.

I updated the trigger to include intBar and now it works perfectly.