DDL Trigger

 DDL Triggers 

According to microsoft, DDL triggers run in response to a variety of data definition language (DDL) events. We can apply DDL triggers for Create, Alter and/or Drop DDL commands. 

DDL trigger is used to perform operation on a specific DDL event(Create,Alter and/or Drop). we can create DDL triggers on table, view , functions etc.

Here in this article we will create DDL trigger for table creation. We can create DDL trigger with one or multiple commands like Create_Table,Alter_Table,Drop_Table or single commands like Create_Table. Below Trigger fires when table gets created, altered or drop. 

create trigger Table_create_DDLTrigger

on Database

for Create_Table,Alter_Table,Drop_Table

As

BEGIN

Print 'Table created, alter or drop'

END

Now create a new table once table created message from DDL trigger gets printed.

create table Table1

(

  Id int ,

  name varchar(5)

)

DLL trigger fire for below alter table script

Alter table Table1

alter column name varchar(10)

DLL trigger fire for below drop table script

drop table Table1

 Very interesting concept from DDL trigger, you can prevent user for performing DDL commands on table, functions, etc

Suppose you want to allow user to access table data but dont want them to Create,Alter or Drop table. To achieve this we will create DDL trigger on table with Rollback commnd.

Create trigger Avoid_Create_Alter_Drop_table

on Database

for Create_Table,Alter_Table,Drop_table

As

Begin

Rollback

print('You do not have permission to change structure of table.')

End

create table table3(

id int,

name varchar(10)

)

-- To drop trigger on database

drop trigger Avoid_Create_Alter_Drop_table on database