Trigger Questions

  • I have a stored procedure that contains a merge statement that is working well. The Merge is your regular

    MERGE Table1 USING

    etc. . . .

    WHEN MATCHED THEN UPDATE . . .

    WHEN NOT MATCHED THEN INSERT . .

    Within the trigger of Table1, I have code that does something specific for inserts (set based) and something else for updates.

    if (exists(select * from Deleted)) set @del = 1

    if (exists(select * from Inserted)) set @ins = 1

    SET @Act =

    CASE

    WHEN (@Ins = 0 and @del = 1) THEN 'DELETE'

    WHEN (@Ins = 1 and @del = 0) THEN 'INSERT'

    WHEN (@Ins = 1 and @del = 1) THEN 'UPDATE'

    ELSE

    CAST(NULL AS VARCHAR(6))

    END

    if @Act is NULL return

    if @Act = 'INSERT' begin

    end

    if @Act = 'UPDATE' begin

    end

    My questions are, when the Merge is called and there are both Inserts and Updates, does the trigger see just the inserts, then the updates or is it just a grab bag of changes? If it is a mixed set, how would the trigger differentiate between an Insert, Update and a Delete?

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • I would recommend, instead of creating one trigger FOR INSERT, UPDATE, DELETE, you create three different triggers. One FOR INSERT (which will be fired only for the inserted rows via the merge), one FOR UPDATE and one FOR DELETE. That way you can get rid of all of that complex code to figure out which operation happened and chose what should be done.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/8/2016)


    I would recommend, instead of creating one trigger FOR INSERT, UPDATE, DELETE, you create three different triggers. One FOR INSERT (which will be fired only for the inserted rows via the merge), one FOR UPDATE and one FOR DELETE. That way you can get rid of all of that complex code to figure out which operation happened and chose what should be done.

    +1,000,000. I have seen so many clients over the years mess this one up - sometimes disastrously so!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • GilaMonster (11/8/2016)


    I would recommend, instead of creating one trigger FOR INSERT, UPDATE, DELETE, you create three different triggers. One FOR INSERT (which will be fired only for the inserted rows via the merge), one FOR UPDATE and one FOR DELETE. That way you can get rid of all of that complex code to figure out which operation happened and chose what should be done.

    Excellent advice!

    Thank-you.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • It depends on how complex the trigger logic and how much different it is for each type of modification whether you want separate triggers or not.

    But you can definitely simplify the setting of 'DELETE' | 'INSERT' | 'UPDATE':

    if (exists(select * from Deleted))

    if (exists(select * from Inserted))

    set @Act = 'UPDATE'

    else

    set @Act = 'DELETE'

    else

    set @Act = 'INSERT'

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

  • ScottPletcher (11/8/2016)


    It depends on how complex the trigger logic and how much different it is for each type of modification whether you want separate triggers or not.

    I disagree on the principle that you are doing work for an unknown-to-me reason. So I would like to know your reason(s) for separating or not based on complexity.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (11/8/2016)


    ScottPletcher (11/8/2016)


    It depends on how complex the trigger logic and how much different it is for each type of modification whether you want separate triggers or not.

    I disagree on the principle that you are doing work for an unknown-to-me reason. So I would like to know your reason(s) for separating or not based on complexity.

    The obvious example is if the only difference in the code was a literal showing 'DELETE' / 'INSERT' / 'UPDATE'. Then I don't see any need for separate triggers at all.

    Or, if the logic is (very) complex overall, and there are only minor difference, I there's more chance that separate code would diverge, with some changes perhaps not being reflected in all triggers.

    If the different modifications require significantly different logic and/or data flows, then separating makes perfect sense. Or if DELETEs, say, are extremely rare, it could make sense to have a separate DELETE trigger so that code is only loaded when genuinely required.

    I disagree with the idea that for all triggers, all modification types should always go in separate triggers.

    O

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

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

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