January 28, 2014 at 8:07 am
Please let me know the max no of triggers we can keep on a table & which will execute first if we have all insert, delete & update triggers on a same table .
January 28, 2014 at 8:18 am
ramana3327 (1/28/2014)
Please let me know the max no of triggers we can keep on a table & which will execute first if we have all insert, delete & update triggers on a same table .
You need to read up and understand what triggers do. The order of execution for different trigger types doesn't make any sense. An update trigger will not fire when you execute an insert statement etc.
To be honest, triggers should be used sparingly. You can in fact have multiple triggers defined for a given type of query but there is no guarantee of the order of execution.
Why do you think you need so many triggers on a table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2014 at 8:18 am
Max number is more than you could ever need.
When there are multiple triggers for the same action (multiple insert for example), you can defined which one runs first and which one runs last, but that's it. If you don't, then there's no defined or guaranteed order in which they will run.
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
January 28, 2014 at 8:31 am
GilaMonster (1/28/2014)
When there are multiple triggers for the same action (multiple insert for example), you can defined which one runs first and which one runs last, but that's it.
Interesting. I didn't realize you could set the order of trigger execution.
http://technet.microsoft.com/en-us/library/ms186762.aspx
Certainly makes sense with regards to replication though. It is a little bit unclear if you could define the order for more than 2 triggers for the same event. Not that I would recommend having that many but it is interesting.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2014 at 8:55 am
Sean Lange (1/28/2014)
GilaMonster (1/28/2014)
When there are multiple triggers for the same action (multiple insert for example), you can defined which one runs first and which one runs last, but that's it.Interesting. I didn't realize you could set the order of trigger execution.
You can't completely. You can specify which is first and which is last, that's all. So for up to 3 triggers you can define the order. Past that, not so much.
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
January 28, 2014 at 9:00 am
GilaMonster (1/28/2014)
Sean Lange (1/28/2014)
GilaMonster (1/28/2014)
When there are multiple triggers for the same action (multiple insert for example), you can defined which one runs first and which one runs last, but that's it.Interesting. I didn't realize you could set the order of trigger execution.
You can't completely. You can specify which is first and which is last, that's all. So for up to 3 triggers you can define the order. Past that, not so much.
Anything beyond that deserves a truck load of frozen pork chops launched at high speed anyway. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2014 at 12:33 pm
You can actually control what order everything is done by triggers. Note that I did NOT say you could control the order of triggers (other than the first of the last).
Instead of agonizing over the order of triggers, just write one trigger with code that executes in the correct order. It does provide the disadvantage of not being able to disable a given trigger but, if you already have triggers that need to be executed in some given order (other than the first or the last), you've already made your life difficult.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2014 at 1:28 pm
Jeff Moden (1/28/2014)
You can actually control what order everything is done by triggers. Note that I did NOT say you could control the order of triggers (other than the first of the last).Instead of agonizing over the order of triggers, just write one trigger with code that executes in the correct order. It does provide the disadvantage of not being able to disable a given trigger but, if you already have triggers that need to be executed in some given order (other than the first or the last), you've already made your life difficult.
Triggers on primary transaction tables are a pet-peeve with me against our software provider. Their focus is "as long as it gets done .... who cares how bad the back end looks.... as long as it runs..." attitude is enough to drive me crazy.
One of their consultants came to me concerned that they were doing too much in the 6+ triggers on a primary table. My suggestion, which is what Jeff has suggested, assemble all of the Insert/Update/Delete code into 3 separate triggers. Well that suggestion fell on deaf ears.
When the system is officially handed over these are the types of things I have to look forward to....
Bottom line is .... KISS. Save yourself the headaches of having to manage all that code if a trigger has to be there in the first place. Often times database developers get lazy and will literately throw code all over the place with the principal "as long as it works".
Table triggers should be very closely scrutinized before they get placed onto a high transaction volume table. In addition the code should go through rigorous tuning to minimize performance hit.
Last but not least, for God sake do not put a cursor into a trigger... I've fired DBAs for less. :w00t: :blink: (yes, I've seen this and it just makes my skin crawl).
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply