Tuesday 8 August 2017

DDL Trigger in sql server

A Data Definition Language trigger is fired in response to Data Definition Language, such as create, drop and alter table or alter table .DDL triggers can be used to perform database auditing, administrative tasks.

DDL trigger are categorized as:

Ø  After Triggers
Ø  Instead of Triggers
Ø  Nested Triggers
Ø  Recursive Triggers

After Trigger

It is a specialized stored procedure that is executed when data in the table associated with trigger is modified. After trigger executes the code associated with it after the event for which it is made happens. We can have multiple after triggers for any single DML operations.

Instead of Trigger

The instead of triggers can be primarily used to perform an action , such as a DML operation on another table or view .This type of trigger can be created on both a table as well as view .

Nested Trigger

Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. We can control whether AFTER triggers can be nested through the Nested Triggers server configuration option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting.

Recursive Triggers

Recursive triggers when a trigger fires and performs a statement that will cause the same trigger to fire, recursion will occur. There are two triggers, Direct and Indirect.

Syntax to Create Trigger



 For the list of all DDL events please visit


Example creating trigger on the database

Create Trigger trg_db_test_tbl_create
      On Database
For CREATE_TABLE
AS
declare @tblname varchar(100);
BEGIN
   Print 'New table created'
END


Trigger is created successfully. Now I am creating a table in database.
  


Creating a trigger for dropping the object
Create Trigger trg_db_test_tbl_drop
 On Database
For DROP_TABLE
AS
BEGIN
   Print 'Table Droped'
END

Now I am dropping the table


We can see the trigger on database level.

We can disable the trigger
DISABLE TRIGGER trg_db_test_tbl_create ON DATABASE

We can enable the trigger
ENABLE TRIGGER trg_db_test_tbl_create ON DATABASE


Drop the trigger
DROP TRIGGER trg_db_test_tbl_create ON DATABASE



No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts