Recently I observed that in one table there are more than 4 triggers are there for after Update event. Can you please let me know why we need to create multiple triggers for one particular event(insert / update / delete) instead of one triggers.
Is there any performance benefits ?
How many triggers can we create on one table ?
what is the max limit to create the triggers for same event(insert / update / delete) ?
Thanks in Advance....
There won't be a performance benefit for multiple triggers, no. In fact, it's a hidden performance cost. Every time you get an UPDATE you'll have four triggers firing. The number of triggers per table is only limited by the number of objects in a database 2,147,483,647.
Remember, the books online is your friend. You can always find this sort of information there: http://msdn.microsoft.com/en-us/library/ms143432.aspx
Personally, I'd be very concerned if I had four triggers on a table. What do they do? Are triggers the right way to get that job done?
answered Aug 07, 2012 at 07:18 PM
Grant Fritchey ♦♦