SQL SERVER – DDL Triggers

We are all well known about the DML Triggers which will be fired automatically whenever Data Manipulation statements like INSERT,UPDATE and DELETE are executed.

DDL triggers are same as that of the DML triggers, but they will fire with the Data definition statements like CREATE, ALTER and DROP instead of INSERT, UPDATE and DELETE
statements.

In general DDL triggers can be used to prevent the changes to the tables like 'DROP TABLE' and 'ALTER TABLE'  and
to audit the changes in the database schema. These triggers will fire only after the transact SQL statements are completed.  

In case of DML triggers, they will be applied on either tables or Views. where as DDL triggers will be applied on either Databases or servers.

Database-scoped DDL triggers are stored in the database in which they are created where as the Server-scoped DDL triggers are stored in the Master database.

We can get the Database-scoped and Server-scoped triggers using sys.triggers and sys.server_triggers respectively.

Now Let us have a look at the syntax for creating a DDL trigger. 

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS
{ sql_statement }

Here is an example for creating a DDL trigger while altering the Emp table 

Create Trigger Trig_AviodChangeEmp
on Database
For alter_table
as
Begin
   Print 'You cannot drop the column in the Table Emp'
   Rollback
End