Trigger

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
There are two (2) types of triggers in Sql Server 2005 DDL (Data Definition Language) and DML (Data Manipulation Language).
DDL is new in Sql Server 2005.

Syntax to create trigger:

CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[ WITH [ ,...n ] ]

{ FOR | AFTER } { event_type | event_group } [ ,...n ]

AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }


When you want to fire the trigger on table level you have to use DML triggers. DML triggers can be on INSERT,UPDATE,DELETE command. While DDL triggers can be used when you want to execute at the time of table,procedure,trigger,function's events like creation , alteration , drop.