Update trigger and concurrency issue

Question

Update trigger and concurrency issue

Seggerman-675349

Hall of Fame

Points: 3563
More actions
April 12, 2019 at 1:57 pm

#3157145
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?
- This topic was modified 5 years, 7 months ago by Seggerman-675349.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99129 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 2

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

ScottPletcher SSC Guru Points: 101091 More actions · Answer 3

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".

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 4

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

Thom A SSC Guru Points: 99129 More actions · Answer 5

Seggerman-675349 wrote:

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