INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL 2005 DDL Triggers

How to monitor DDL events in your DB by ptheriault
Posted: 31 Jul 07 (Edited 31 Jul 07)

Beginning  SQL Server 2005 we now have the ability to create triggers at the database and server level to monitor for DDL events.  Here is a DDL trigger to monitor for Create, Alter and Drop table events in your databases.

First Create a log table. ( I put mine in the msdb database for this example but you can either create your own log db or place it in your user db)

CODE

USE msdb
GO
CREATE TABLE ddl_log
(ID int idenity(1,1) PRIMARY KEY CLUSTERED,
 PostTime datetime,
 DB_User nvarchar(100),
 Event nvarchar(100),
 TSQL nvarchar(2000));
Here is the DDL Trigger.  The trigger will add a record to the log table and send an e-mail with the DDL statement.  If you want to ROLLBACK the DDL you can do so by adding the ROLLBACK statement before the insert.
Make sure you edit the email address.

CODE

Create TRIGGER DDL_Notify
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE,CREATE_TABLE
AS
DECLARE @data XML,
        @tableHTML  NVARCHAR(MAX) ;

SET @data = EVENTDATA()

INSERT msdb.dbo.ddl_log (PostTime, DB_User, Event, TSQL)
VALUES (GETDATE(), CONVERT(nvarchar(100), USER_NAME()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

SET @tableHTML =
    N'<H1>DDL Table Event</H1>' +
    N'<table border="1">' +
    N'<tr><th>Post Time</th><th>User</th>' +
    N'<th>TSQL</th><th></tr>' +
    CAST ( ( SELECT td = PostTime,       '',
                    td = DB_User, '',
                    td = TSQL, ''
              FROM msdb.dbo.ddl_log
              WHERE id = (select max(id) from msdb.dbo.ddl_log)
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Default',
    @recipients='dba@youradress.com',
    @subject = 'DDL Table Event',
    @body = @tableHTML,
    @body_format = 'HTML'

;


Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close