April 12, 2019 at 1:57 pm
I built a table - and the DBA added a mandated UPDATE trigger to automatically update standard audit dates. I update the table from a number of sources with several statements. Every time, some of them produce the following error:
A Concurrency problem exists. [my table] was just changed by another user.
the code in the trigger:
SELECT @newUpdateDt = UpdateDt
FROM inserted
SELECT @oldUpdateDt = UpdateDt
FROM deleted
IF @oldUpdateDt <> @newUpdatedt
BEGIN
-- 'A Concurrency problem exists. %s was just changed by another user.'
RAISERROR (50103, 16, 1, '[my table]')
Return
END
how do I fix this?
April 12, 2019 at 2:02 pm
That trigger is flawed, it assumes one row will only be affected. If inserted or deleted contain more than 1 row, what do you suppose these 2 statements will do?
SELECT @newUpdateDt = UpdateDt
FROM inserted
SELECT @oldUpdateDt = UpdateDt
FROM deleted
Plus the fact that the neither have an ORDER BY clause (so the order is random), so even if you updated just 2 rows there's no guarantee that @newUpdateDt and @oldUpdatedt will have any relation in regards to the row they represent.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 12, 2019 at 2:07 pm
You'd need to define what you want the trigger to do and we can then help you. As Thom noted, this is flawed code.
Some basics: https://www.sqlservercentral.com/articles/learning-about-dml-triggers
April 12, 2019 at 3:25 pm
I don't know that you'll be able to fix it, particularly without the details of how the "standard audit dates" are maintained.
It seems like this is an integrity check to make sure that between the time a row is read and it is updated, no other changes take place. If that is true, you wouldn't want to get around it. If, for example, you read the row and the value was 10, you intended to add 2 to that value, but in the meantime someone else updated the value to 20, you presumably wouldn't want to overwrite that new value with 12.
How long is the delay from the time you read data to the time you first update it and last update it. You said "I update the table from a number of sources with several statements." How do those UPDATEs affect the audit dates?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 12, 2019 at 9:25 pm
thanks guys
the fix was to pass in Updatedt=Updatedt
the trigger itself was provided as required by the DBA
wacky but that's the way they do things so I wans't going to go against the flow
April 12, 2019 at 9:31 pm
thanks guys the fix was to pass in Updatedt=Updatedt the trigger itself was provided as required by the DBA wacky but that's the way they do things so I wans't going to go against the flow
None of that changes that the trigger is flawed. It's not going to work properly. A good DBA knows that; which makes me have concerns for the decisions your DBA makes.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply