Update trigger and concurrency issue

  • Seggerman-675349

    Hall of Fame

    Points: 3503

    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?

  • Thom A

    SSC Guru

    Points: 98011

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714067

    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: 97936

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Seggerman-675349

    Hall of Fame

    Points: 3503

    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: 98011

    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.

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

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