Trigger

Shubham Srivastava

14y1.6k

hi,

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
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 Trivedi14y ago
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 2,147,483,647.

hope this help.
Vulpes14y ago
For practical purposes the number of triggers is unlimited.

There is a theoretical limit of 2,147,483,647 objects per database which includes tables, views, stored procedures, constraints, triggers etc. but this is hardly likely to be a problem in practice :)