Implementing a trigger on a status change/insert rec to update a flag

  • Hi wondering if someone can give some insight into what works better for a trigger below: 

    example:
    Simplifying
    Table  Customer
         id                    int
        Change_Flag   bit
         status              char(1)

    Generally records are 
     id                    (some unqiue id)  -- this is an indexed column
    change_flag  0
    status            null 

    During the day we may get new records inserted with status with "X" or updated for the status column with "X"
    when status  = "X"     then change_flag needs to be set to 1

    I'm interested in a after  insert, update   -- which would be a better implementation ? 
    Or is there a best practice implementation that would be better. 

     alter trigger  trg_up_in_changeflag on example_table after update, insert

     as

     begin

       IF UPDATE(status)

       begin

          update test_table

      set flag =1

      from example_table  et

      where status = 'X'

      and EXISTS (select 1 from inserted i wherei.id = et.id)

    end

    end

    or

     begin

       IF UPDATE(status)

       begin

          update test_table

      set flag =1

      from example_table  et , 
    inserted i
     
        wherei.id = et.id

        end

    end

    Thanks for the ideas!

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • This one is not so popular 🙁 

    I'm going to go with

    IF UPDATE(status)

    begin

    update example_table

    set flag =1

    from example_table et

    where status = 'X'

    and EXISTS (select 1 from inserted i wherei.id = et.id)

    end

    The index on 'id' in example_table will make the update quick for setting the flag in option 2 but option one
    I believe  "EXISTS"   i faster as evals true/false rather than the whole join of the query is that right? 

    I have not wrote T-SQL in awhile so figure some of those more fluent can confirm

    thanks!

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • I'd add the standard "nocount" setting, but other than that it looks good:


    alter trigger trg_up_in_changeflag
    on example_table
    after update, insert

    as

    set nocount on

    begin

    IF UPDATE(status)

    begin

    update test_table

    set flag =1

    from example_table et

    where status = 'X'

    and EXISTS (select 1 from inserted i where i.id = et.id)

    end /*if*/

    end /*begin*/

    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 - Wednesday, December 19, 2018 1:09 PM

    I'd add the standard "nocount" setting, but other than that it looks good:


    alter trigger trg_up_in_changeflag
    on example_table
    after update, insert

    as

    set nocount on

    begin

    IF UPDATE(status)

    begin

    update test_table

    set flag =1

    from example_table et

    where status = 'X'

    and EXISTS (select 1 from inserted i where i.id = et.id)

    end /*if*/

    end /*begin*/

    Hey great thanks so much Scott!  
    Good call on nocount...   reduce network traffic with those unneeded messages 😛

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

Viewing 4 posts - 1 through 3 (of 3 total)

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