Trigger

Question

hi How many number of trigger we can apply on a table ? and give some description on it.

Senthilkumar · Answer

Hi Shubham 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 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. for maximum specification of sql entities: http://www.sql-server-helper.com/sql-server-2005/maximum-capacity-specifications.aspx http://sqlexplore.wordpress.com/2011/04/01/sql-server-what-are-triggers-how-many-triggers-you-can-have-on-a-table-how-to-invoke-a-trigger-on-demand/

Jignesh Trivedi · Answer

hi Database objects include objects such as tables views stored procedures extended stored procedures user-defined functions triggers rules defaults and constraints. The sum of the number of all objects in a database cannot exceed 2147483647. hope this help.

Vulpes · Answer

For practical purposes the number of triggers is unlimited. There is a theoretical limit of 2147483647 objects per database which includes tables views stored procedures constraints triggers etc. but this is hardly likely to be a problem in practice :)