How many triggers can be implemented in a single table

  • 1. Well the question is simple. How many triggers can be implemented in a single table.

    2. If a table has 5 Insert triggers, which trigger will execute first.

  • 1. Well the question is simple. How many triggers can be implemented in a single table.

    Effectively unlimited, although you can only have 1 INSTEAD OF INSERT, 1 INSTEAD OF UPDATE and 1 INSTEAD OF DELETE trigger

    2. If a table has 5 Insert triggers, which trigger will execute first.

    You can use the stored procedure sp_settriggerorder to define the trigger that fires FIRST and/or LAST... the rest will fire in an undefined sequence.

    If you are looking to have that many triggers, and the sequence is important, I would seriously consider combining them into a single trigger.

    The "triggers are evil" brigade will probably suggest you move the logic elsewhere, and do away with the triggers entirely.

  • TRIGGERS ARE EVIL!

    But yeah, I agree with Ian, consolidate triggers wherever possible. Although you can set the trigger order, any time you modify/insert/delete a trigger, that order gets changed and you have to set it again. (At least it does in 2000, not sure if that is different in 2005).

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • If the sequence matters, put it in the trigger code, and don't count on firing sequence.

    Most of the time, it's better to have the code in the insert proc instead of a trigger. Not always true, but true more often than not.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • triggers are bad - because ... they are really hard to managed in big database

  • Marcin Gol [SQL Server MVP] (10/1/2009)


    triggers are bad - because ... they are really hard to managed in big database

    What makes them harder to manage in a large database, as opposed to a smaller one?

  • Ian Scarlett (10/2/2009)


    Marcin Gol [SQL Server MVP] (10/1/2009)


    triggers are bad - because ... they are really hard to managed in big database

    What makes them harder to manage in a large database, as opposed to a smaller one?

    Yeah, I dont understand that either, the principle is the same. reardless of size. If triggers are written correctly, you should have no problems.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • I see triggers as a mixed bag. I use them but only when I think they are the most elegant way of handling a problem. I think that the

    triggers are bad - because ... they are really hard to managed in big database

    comment was probably referring to the fact that if you are not used to looking for them/using them, they can be forgotten and you can easily have logic occurring that seems to be outside what someone would look for. In a small database where you can be intimately familiar with all the tables it's easier to remember that they are there and operating. In a database with hundreds of tables and only a hand full of triggers, well... Things can be forgotten easier. Just a guess as to what the poster was thinking though.

  • Please note: 4 year old thread.

    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

Viewing 9 posts - 1 through 8 (of 8 total)

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