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

Question

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

Thanks in advance.

Answer 1

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