Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How many triggers can be implemented in a single table Expand / Collapse
Author
Message
Posted Wednesday, September 30, 2009 3:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:09 AM
Points: 18, Visits: 115
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.
Post #795602
Posted Wednesday, September 30, 2009 3:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
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.



Post #795607
Posted Wednesday, September 30, 2009 9:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:20 PM
Points: 1,519, Visits: 4,085
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #795817
Posted Wednesday, September 30, 2009 9:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #795833
Posted Thursday, October 1, 2009 4:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 3, 2010 3:50 AM
Points: 146, Visits: 178
triggers are bad - because ... they are really hard to managed in big database
Post #796683
Posted Friday, October 2, 2009 1:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
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?



Post #796772
Posted Friday, October 2, 2009 2:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:13 AM
Points: 2,704, Visits: 1,149
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.


--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #796786
Posted Thursday, August 8, 2013 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 4, 2013 3:48 PM
Points: 1, Visits: 61
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.
Post #1482388
Posted Thursday, August 8, 2013 9:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
Please note: 4 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1482392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse