DDL Triggers - SQL Server

DDL Triggers are similar to regular triggers that fires in response to an event. Unlike DML Triggers they don’t fire in response to Update, Delete, and Insert statements on a table. They fire in response to DDL events like CREATE, ALTER, DROP etc.,
 

DDL Triggers helps to audit the database level changes or you can restrict to perform any kind of drop operation or to restrict to perform any kind of schema updates. DDL Triggers can fire in response to a DDL Event in the current database or in the current server.

 

Note : DDL Triggers do not fire in response to events that affect temporary tables.

 

The scope of the DDL Trigger is depends on the event, for example if we setup a Create Table trigger created to fire in response to a Create Table event that can be occurs in the database or in the server, if we take Create Login trigger then it will occur only at server level not database level.

 

Example 

USE kalyandb
GO
ALTER TRIGGER prevent_drop ON DATABASE FOR DROP_TABLE AS
 PRINT 'You must Disable the trigger "Prevent Drop" to drop this table'
 ROLLBACK
 

Viewing Triggers
 

SELECT * FROM SYS.triggers 

View Definition of a Trigger

SELECT DEFINITION FROM SYS.sql_modules WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.TRIGGERS WHERE NAME='PREVENT_DROP')
 

To Know the scope of trigger

 

SELECT type, name, parent_class_desc FROM sys.triggers

UNION

SELECT type, name, parent_class_desc FROM sys.server_triggers ;