DDL Triggers with practical example

DDL triggers let you watch what users do, but instead of watching what they do to data, you can watch what they do to the system. They let us protect and monitor changes to the server or database structure by firing when a user executes any DDL statement. The list of DDL statements you can monitor is quite long. (There are server-level events, such as creating and altering logins, as well as for the database, including creating and modifying tables, indexes, views, procedures, and so on.

For a full list, check SQL Server 2008 Books Online in the “DDL Events Groups” topic.)

DDL triggers are of no value in protecting data values, because they don’t fire for operations

where data is changed or manipulated. They are, however, good for monitoring and preventing

changes to the system, even by users who have the rights to do so. For example, consider the all too-frequent case where the manager of the IT group has system administration powers on the database, though he can barely spell SQL (if this power wasn’t granted, it would seem like a slight to the abilities/power of this manager). Now, let’s assume that this manager is just pointing and clicking his way around the UI, and one click is to the wrong place and all of a sudden, your customer table joins the choir invisible. Now you have to restore from a backup and waste a day cleaning up the mess, while trying to figure out who dropped the table. (OK, so if you have constraints on your table, you can’t actually drop it that easily. And yes, to be honest most every DBA has dropped some object in a production database. That ends the honest part of this section.

1) Preventing a DDL Action:

With a simple DDL trigger, we can prevent the accidental drop of the table by trapping for the event and stopping it or we can log who it was that dropped the table. In the first example, I will create a DDL trigger that will prevent any alterations to the schema without the user going in and manually disabling this trigger. It is a great safeguard to secure your objects from accidental change.

CREATE TRIGGER tr_server$allTableDDL_prevent –note, not a schema owned object

ON DATABASE

AFTER CREATE_TABLE, DROP_TABLE, ALTER_TABLE

AS

BEGIN

BEGIN TRY –note the following line will not wrap

RAISERROR (‘The trigger: tr_server$allTableDDL_prevent must be disabled before making any Modifications to Database’,16,1)

END TRY

BEGIN CATCH

DECLARE @ERROR_MESSAGE varchar(8000)

SET @ERROR_MESSAGE = ERROR_MESSAGE()

RAISERROR (@ERROR_MESSAGE,16,1)

END CATCH

END

Now we try to create a simple table:

CREATE TABLE dbo.test –dbo for simplicity of example

(

testId int identity CONSTRAINT PKtest PRIMARY KEY

)

We get the following error message:

Msg 50000, Level 16, State 1, Procedure tr_server$allTableDDL_prevent, Line 19

The trigger: tr_server$allTableDDL_prevent must be disabled before making any

Modifications to Database

1) Recording a DDL Action:

The second case, and just as useful, is to log DDL that is executed in a database so you can see what has been done. Although stopping DDL is something I usually do in a production database, logging changes is something I often do in a development environment. Not that logging is never useful in a production environment; it is just that it shouldn’t be as necessary, since tables in the production system should be very stable and changed only in an organized manner, not just randomly by a user or a DBA. Sometimes I will use DDL logging to catch things that are routine such as index changes so I will know to watch the new indexes especially closely for a while to see whether they are valuable.

Let’s look at creating a trigger similar to the one created . The difference is that this time we will have the trigger monitor DDL changes, not prevent them. First, let’s drop the trigger created previously:

DROP TRIGGER tr_server$allTableDDL_prevent ON DATABASE

Now, we create a table to contain the history of changes to our table:

CREATE TABLE dbo.TableChangeLog

(

TableChangeLogId int identity

CONSTRAINT pkTableChangeLog PRIMARY KEY (TableChangeLogId),

ChangeTime datetime,

UserName sysname,

Ddl varchar(max)–so we can get as much of the batch as possible

)

And we build another trigger to fire when a user creates, alters, or drops a table:

–not a schema bound object

CREATE TRIGGER tr_server$allTableDDL

ON DATABASE

AFTER CREATE_TABLE, DROP_TABLE, ALTER_TABLE

AS

BEGIN

SET NOCOUNT ON –to avoid the rowcount messages

SET ROWCOUNT 0 –in case the client has modified the rowcount

BEGIN TRY

–we get our data from the EVENT_INSTANCE XML stream

INSERT INTO dbo.TableChangeLog (ChangeTime, userName, Ddl)

SELECT getdate(), user,

EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,

‘nvarchar(max)’)

END TRY

BEGIN CATCH

DECLARE @ERROR_MESSAGE varchar(8000)

SET @ERROR_MESSAGE = ERROR_MESSAGE()

RAISERROR (@ERROR_MESSAGE,16,1)

END CATCH

END

Now we run this to create the dbo.test table:

CREATE TABLE dbo.test

(

id int

)

GO

DROP TABLE dbo.test

We check out the TableChangeLog data to see what has changed:

SELECT * FROM dbo.TableChangeLog

This shows us our commands:

TableChangeLogId

ChangeTime

UserName

Ddl

1

57:24.0

dbo

CREATE TABLE dbo.test ( id int )

2

57:24.0

dbo

DROP TABLE dbo.test

Now we can see what users have been up to in the database without them having to do anything special to cause it to happen (or without them even knowing, either).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s