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.