Using DDL Triggers in SQL Server

It has been more than two decades since Relational Database Management
Systems (RDBMS) have made transition from the laboratory to becoming the data
store of choice for all large and small businesses. And with time, database
servers are increasingly becoming sophisticated and are providing more
functionalities and features to the developers, administrators and architects.
It would benefit us a lot if we learn what new is coming new with each version.

In Microsoft SQL Server , the versions in use across development houses in
India are primarily 2000 and 2005, and in a few cases, 2008. The move from
Microsoft SQL Server 2000 to 2005 was a big leap with many new functionalities
and features introduced. The concept of DDL Triggers was one such new feature.

We know about triggers as they exist in SQL Server 2000 and its previous
versions — triggers that fire in response to INSERT, UPDATE and DELETE events.
They are now called as DML triggers, as they fire in response to DML events. The
new triggers that we are discussing now fire in response to DDL events, for
example CREATE TABLE. Hence they are called DDL triggers. The code samples
presented here run on an instance of SQL Server 2008, but they would also run on
SQL Server 2005 also.

Before we get into serious business usage, I would demonstrate a simple DDL
trigger that displays a message in response to the CREATE TABLE event.

CREATE TRIGGER DDLDemo

ON DATABASE

FOR CREATE_TABLE

AS

PRINT 'Table Created!'

After the trigger has been created at the database level, let us now create a
table and see if the trigger is getting executed.

CREATE TABLE employee

(

EmployeeID int primary key,

EmployeeName nvarchar(30)

)

As you can see, the trigger gets fired in response to any create table
statement.

EVENTDATA

If you have used DML triggers extensively you would know there would be times
when you need to know which rows are being inserted, updated or deleted; what
are the old values and what are the new values. In DML triggers, we use the
magic tables inserted and deleted to answer these queries.

In DDL triggers you would need to know what is the activity that is being
carried out, who is carrying it out the activity, when is the activity being
carried out, what is the query that is being executed, and so on.

The EVENTDATA() function answers these questions when fired from within a
trigger. Let us try it out.

ALTER TRIGGER DDLDemo

ON DATABASE

FOR CREATE_TABLE

AS

SELECT EVENTDATA()

GO

DROP TABLE employee

GO

CREATE TABLE employee

(

EmployeeID int primary

key,

EmployeeName nvarchar(30)

)

GO

When you create the table now, the trigger executes the EVENTDATA() function
and displays some details:

Click on the XML link in the result. SQL Server Management Studio will open a
new window with the XML results.

CREATE_TABLE

2009-05-

14T07:23:08.340

53

AMARESH\SQL2008

AMARESH\Administrator

dbo

Name>payroll

dbo

Name>employee

TABLE



QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

CREATE TABLE employee

(

EmployeeID int primary key,

EmployeeName nvarchar(30)

)

You would see this has all the data you would need about the event that fired
the trigger.

SQL Server 2005 and 2008 have a set of extensive functions that let you
manipulate the results of EVENTDATA() and extract the info you are interested
in. Refer to books online for details on the XML data type and the associated
methods.

Applications

So where do you use DDL triggers? You can use them whenever and wherever you
want to take custom actions against execution of DDL events. The following two
are classic examples.

1. You want to audit various DDL events and maintain a track of who is
creating, altering and dropping various database objects and when. Create a
table for maintaining your audit records and insert into this table from within
DDL triggers.

2. You want to enforce certain rules on various database objects.

Like, you want to enforce thatevery table being created has a primary key. In
the CREATE TABLE trigger, you would then check if the table has a primary key.
You will rollback the transaction if your rule is violated.

Amresh Patnaik