How to create DDL Triggers in sql server 2008 R2


DDL (Data Definition Language) triggers are same as regular trigger that fire automatically whenever any event occurs. DML triggers fire in response to UPDATE, INSERT and DELETE whereas DDL triggers fire in response of CREATE, ALTER and DROP.

Data changes so frequently in the real world that keeping track of each and every change is very difficult. Its responsibility of DBA to keep eye on changes implemented on production server.

C2 Audit Mode and Profiler can be used to track on changes in SQL Server which affect performance of the SQL server. Profiler traces data from last restart of sql server and adds burden on server due to which started writing script to capture the changes. Also filtering data in trace file is difficult to manage.

By creating DML (Data Manipulation Language) triggers on tables for INSERT, UPDATE and DELETE statement to audit the data change.

SQL Server 2005 introduces DDL triggers as new feature, Using DDL triggers you can prevent changes to your database schema also you can track the changes done in your database level or object level.
DDL triggers which made auditing little bit easier to track the object level change.

Lets do some practical to understand it better.
1. Create test database to implement the practicals as real scenario.

create database testdb

2. Create a table to store all changes done by user, run below command to create table with two columns, first one is rowid which will help while searching data in the table and other one to store change data in xml format.

use testdb
create table ddlog (rowid int identity, Event_details xml )

3. Create trigger on database to keep a track of object level changes like if any existing table dropped by someone it will get capture in the table while executing drop command.

create trigger mytrig on database
for create_table, drop_table
insert into ddlog(event_details)

4. Now we have created triggers on database, let’s check the functionality whether it works in our favor or not.
5. Execute below query to create table on test database.

Create table st1 (col1 varchar(10))

6. Execute below query to check whether change got capture or not, you will see one record in ddlog table.

SELECT * from ddlog

To see the login name who has execute the create command on database execute below query.

SELECT event_details.query(‘EVENT_INSTANCE/LoginName’)’login name’ from ddlog

7. Execute below query to drop table on test database.

drop table st1

Now check whether details of drop tables are in the table or not with login details. This will help you to identify time and command when it was executed.

Leave a Reply




9 + = 10