It is also a type of Stored Procedure but it will get executed automatically based on a event. Execution of a trigger is termed as firing technically.
A trigger will fire based on DML operations like INSERT, UPDATE and DELETE. Triggers can be created on tables and views.
A trigger created for UPDATE operation on a table will get executed/fired automatically whenever any UPDATE command is used on the table.
How many triggers can be created on a table?
A table can have any number of triggers on it. Too many triggers on a table also lead to performance issues.
In SQL Server 2008 there is no particular restriction for number of triggers on a table but the total number of objects a table can have is 2,147,483,647. Objects include everything like tables, views, SP’s, triggers, rules, constraints and user defined functions.
What is Nested trigger?
A trigger can call another trigger and in turn that can call another trigger. These are called nested triggers.