Answer Description: In any database including SQL Server 2005 a trigger is procedure that initiates on INSERT, DELETE or UPDATE actions. Before SQL Server 2000 Triggers are also used to maintain the referential integrity. We can not execute triggers explicitly; the DBMS automatically fires the trigger when data modification events (INSERT, DELETE or UPDATE) happened in the associated table.
Triggers are same as stored procedures in term of procedural logic that is stored at the database level. Stored procedures are executed explicitly and triggers are event-drive.
Triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
See Create Trigger for more