Difference between database level trigger and server level trigger in SQL Server

Can anyone please tell me the difference between database level trigger and server level trigger in SQL Server ?

Thanks in advance.

Answer

SQL Server 2005 introduced DML Triggers that can be set to fire on your chosen DDL events such as CREATE_TABLE, ALTER_TABLE, DROP_TABLE, ALTER_DATABASE, CREATE_LOGIN etc.

DDL Triggers can be set within 2 scopes:

1. Server scope: Triggers created with Server scope must target server DDL events such as CREATE_DATABASE or CREATE_LOGIN
2. Database scope: Triggers created with database scope must target database level events such as CREATE_TABLE or ALTER_PROC.

See the full list of SQL Server DDL Trigger Events (including their scope) on msdn here.

Syntax of a DDL trigger:

CREATE TRIGGER [TriggerName]
ON [Scope (Server|Database)]
FOR [EventName...],
AS
-- code for your trigger response here