SQL Server 2005 DDL Trigger Workbench

Av rating:
Total votes: 98
Total comments: 32

Downloads
- DDLTrigger_Source

25 May 2007

/* How about automatically tracking and logging all database changes,  
including changes to tables, views, routines, queues and so on? With SQL 
Server 2005 it isn't that hard, and we'll show how it is done. If you  
haven't got SQL Server 2005, then get SQL Server Express for free. It  
works on that! While we're about it, we'll show you how to track all 
additions, changes and deletions of Logins and Database Users, using 
a similar technique. 

Contents 
   Logging all changes to the database with source code. 
   Preventing changes to database objects, 
   Logging all changes to the Logins and database users, 
   Further reading 
  

Logging all changes to the database with source code. 
----------------------------------------------------- 

To start off, we'll write a simple trigger that tracks all database  
events. This will include creating, altering or dropping an  
APPLICATION_ROLE, ASSEMBLY, AUTHORIZATION_DATABASE, CERTIFICATE,  
CONTRACT, FUNCTION, INDEX, MESSAGE_TYPE, PARTITION_FUNCTION,  
PARTITION_SCHEME, PROCEDURE, QUEUE, REMOTE_SERVICE_BINDING, 
ROLE, ROUTE, SCHEMA, SERVICE, STATISTICS, TABLE, TRIGGER, USER,  
VIEW,  or XML_SCHEMA_COLLECTION. It will also record the creation,  
or dropping of an EVENT_NOTIFICATION, SYNONYM, or TYPE and track  
all GRANT_DATABASE, DENY_DATABASE, and REVOKE_DATABASE DDL. 

The new DDL triggers work very like the DML triggers you know and love. 
The most radical change is that the details of the event that fired the  
trigger are available only in XML format. You have to get serious with  
XPath queries to extract the XML which is in the format... 

<EVENT_INSTANCE> 
    <EventType>type</EventType> 
    <PostTime>date-time</PostTime> 
    <SPID>spid</SPID> 
    <ServerName>name</ServerName> 
    <LoginName>name</LoginName> 
    <UserName>name</UserName> 
    <DatabaseName>name</DatabaseName> 
    <SchemaName>name</SchemaName> 
    <ObjectName>name</ObjectName> 
    <ObjectType>type</ObjectType> 
    <TSQLCommand>command</TSQLCommand> 
</EVENT_INSTANCE> 
*/ 

--Before you do anything else, create a database called TestLogging 

USE [TestLogging] 
GO 
/*now we will create a table that will be a change log. We will put  
in it the detail of each DDL SQL Statement and the user that did it. 
We'll trap the login and the original login just to check for context  
switching. We'll record the type of object, the type of event and the  
object name, and, of course the SQL that did it! Who needs source 
control?*/ 
CREATE TABLE [dbo].[DDLChangeLog] 
    ( 
      [DDLChangeLog_ID] [int] IDENTITY(1, 1) 
                              NOT NULL, 
      [InsertionDate] [datetime] NOT NULL 
        CONSTRAINT [DF_ddl_log_InsertionDate]  
            DEFAULT ( GETDATE() ), 
      [CurrentUser] [nvarchar](50) NOT NULL 
        CONSTRAINT [DF_ddl_log_CurrentUser]   
            DEFAULT ( CONVERT([nvarchar](50), USER_NAME(), ( 0 )) ), 
      [LoginName] [nvarchar](50) NOT NULL 
        CONSTRAINT [DF_DDLChangeLog_LoginName]   
            DEFAULT ( CONVERT([nvarchar](50), SUSER_SNAME(), ( 0 )) ), 
      [Username] [nvarchar](50) NOT NULL 
        CONSTRAINT [DF_DDLChangeLog_Username]   
            DEFAULT ( CONVERT([nvarchar](50), original_login(),(0)) ), 
      [EventType] [nvarchar](100) NULL, 
      [objectName] [nvarchar](100) NULL, 
      [objectType] [nvarchar](100) NULL, 
      [tsql] [nvarchar](MAX) NULL 
    ) 
ON  [PRIMARY] 

GO 
/* now we'll create the trigger that fires whenever any database level 
DDL events occur. We won't bother to record CREATE STATISTIC events*/ 
CREATE TRIGGER trgLogDDLEvent ON DATABASE 
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
    DECLARE @data XML 
    SET @data = EVENTDATA() 
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 
        <> 'CREATE_STATISTICS'  
        INSERT  INTO DDLChangeLog 
                ( 
                  EventType, 
                  ObjectName, 
                  ObjectType, 
                  tsql 
                ) 
        VALUES  ( 
                   @data.value('(/EVENT_INSTANCE/EventType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 
                              'nvarchar(max)') 
                ) ; 
GO 

--let's create a Table, view, and procedure, and then drop them, and after 
-- that, see what was recorded in the log 
USE [TestLogging] 
GO 
CREATE TABLE [dbo].[PublicHouses]--the test Table 
    ( 
      [pubname] [varchar](100) NOT NULL, 
      [Address] [varchar](100) NOT NULL, 
      [postcode] [varchar](20) NOT NULL, 
      [outcode] VARCHAR(4) 
    ) 
ON  [PRIMARY] 
GO 
USE [TestLogging] 
GO 
CREATE VIEW [dbo].[vCambridgePubs]--the test view 
AS  SELECT TOP ( 100 ) PERCENT 
            pubname, 
            Address + '  ' + postcode AS Expr1 
    FROM    dbo.PublicHouses 
    WHERE   ( postcode LIKE 'CM%' ) 
GO 
CREATE PROCEDURE spInsertPub--the test stored procedure 
    @pubname VARCHAR(100), 
    @Address VARCHAR(100), 
    @postcode VARCHAR(20) 
AS  
    INSERT  INTO PublicHouse 
            ( 
              pubname, 
              Address, 
              Postcode, 
              outcode 
            ) 
            SELECT  @pubname, 
                    @Address, 
                    @Postcode, 
                    LEFT(LEFT(@Postcode,  
                       CHARINDEX(' ',  
                       @Postcode + ' ') - 1), 
                        4) 
GO 
DROP VIEW vCambridgePubs 
GO 
DROP PROCEDURE spInsertPub ; 
GO 
DROP TABLE PublicHouses ; 
GO 
-- now, having done all that we can then see what happened. As you know, 
-- this is the only way you'll ever see the current build statements for 
-- your tables! Now try changing the database objects via SSMS and have a 
-- look at the SQL DDL that gets executed! 

SELECT  * 
FROM    DDLChangeLog 
ORDER BY insertionDate  

-- To help, here is a better rendering of the log. We create an HTML table 
--and format it up prettily 
DECLARE @HTMLCode VARCHAR(MAX) 
SELECT  @HTMLCode = COALESCE(@HTMLCode, ' <style type="text/css"> 
    <!-- 
    #changes{ 
     border: 1px solid silver; 
     font-family: Arial, Helvetica, sans-serif; 
     font-size: 11px; 
     padding: 10px 10px 10px 10px; 
    } 
    #changes td.date{ font-style: italic; } 
    #changes td.tsql{ border-bottom: 1px solid silver; color: #00008B; } 
    --> 
    </style><table id="changes"> 
') + '<tr class="recordtop"> 
<td class="date">' + CONVERT(CHAR(18), InsertionDate, 113) + '</td> 
<td class="currentuser">' + currentUser + '</td> 
<td class="loginname">' + LoginName 
        + CASE WHEN loginName <> UserName THEN '(' + UserName + ')' 
               ELSE '' 
          END + '</td> 
<td class="eventtype">' + EventType + '</td> 
<td class="objectname">' + ObjectName + ' (' + objectType + ')' 
        + '</td></tr> 
<tr class="recordbase"><td colspan="6" class="tsql"><pre>' + tsql 
        + '</pre></td></tr> 
' 
FROM    DDLChangeLog 
ORDER BY insertionDate ; 
SELECT  @HTMLCode + ' 
</table>' 
GO 
--Which gives this...

25 May 2007 10:47:	dbo	SIMPLETALK\RobynPage	CREATE_TABLE	PublicHouses (TABLE)
CREATE TABLE [dbo].[PublicHouses]--the test Table ( [pubname] [varchar] (100) NOT NULL, [Address] [varchar] (100) NOT NULL, [postcode] [varchar] (20) NOT NULL, [outcode] VARCHAR (4) ) ON [PRIMARY]
25 May 2007 10:47:	dbo	SIMPLETALK\RobynPage	CREATE_VIEW	vCambridgePubs (VIEW)
CREATE VIEW [dbo].[vCambridgePubs]--the test view AS SELECT TOP (100) PERCENT pubname, Address + ' ' + postcode AS Expr1 FROM dbo.PublicHouses WHERE (postcode LIKE 'CM%')
25 May 2007 10:47:	dbo	SIMPLETALK\RobynPage	CREATE_PROCEDURE	spInsertPub (PROCEDURE)
Create procedure spInsertPub--the test stored procedure @pubname varchar (100), @Address varchar (100), @postcode varchar (20) as insert into PublicHouse (pubname, Address, Postcode, outcode) Select @pubname, @Address, @Postcode, left (left (@Postcode, charindex (' ',@Postcode+' ')-1),4)
25 May 2007 10:47:	dbo	SIMPLETALK\RobynPage	DROP_VIEW	vCambridgePubs (VIEW)
Drop View vCambridgePubs
25 May 2007 10:47:	dbo	SIMPLETALK\RobynPage	DROP_PROCEDURE	spInsertPub (PROCEDURE)
DROP PROCEDURE spInsertPub;
25 May 2007 10:47:	dbo	SIMPLETALK\RobynPage	DROP_TABLE	PublicHouses (TABLE)
DROP TABLE PublicHouses ;


--once we finish logging we can... 
--Drop the trigger. 
DROP TRIGGER trgLogDDLEvent ON DATABASE 
GO 
--Drop table ddl_log. 
DROP TABLE DDLChangeLog 
GO 
/* 
Of course, this can be very valuable for Database Development work. 

Preventing changes to database objects, 
--------------------------------------- 

BOL seem to be very proud of their example code that prevents a table 
being altered, though, if you have security nailed down properly, this 
shouldn't happen anyway. */ 

CREATE TRIGGER trgNoMonkeying ON DATABASE 
    FOR DROP_TABLE, ALTER_TABLE 
AS 
    DECLARE @Message VARCHAR(255) 
    SELECT  @message = 'You are forbiddent to alter or delete the ''' 
            + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 
                                'nvarchar(100)') + ''' table' 
    RAISERROR ( @Message, 16, 1 ) 
    ROLLBACK ; 
GO 



CREATE TABLE NewTable ( Column1 INT ) ; 
GO 
DROP TABLE NewTable 
/* 
'Msg 3609, Level 16, State 2, Line 1 
The transaction ended in the trigger. The batch has been aborted. 
Msg 50000, Level 18, State 1, Procedure trg, Line 8 
You are forbiddent to alter or delete the 'NewTable' table' 

Logging all changes to the Logins and database users 
---------------------------------------------------- 

Another good use for triggers is to provide information about security 
 events*/ 
GO 
USE master 
GO 
--in MASTER, we'll creat a log for all the databases security events.  
CREATE TABLE [DDLSecurityLog] 
    ( 
      [DDLSecurityLog_ID] [int] IDENTITY(1, 1) 
                                NOT NULL, 
      [InsertionDate] [datetime] NOT NULL 
           CONSTRAINT [DF_ddl_log_InsertionDate]  
               DEFAULT ( GETDATE() ), 
      [CurrentUser] [nvarchar](50) NOT NULL 
           CONSTRAINT [DF_ddl_log_CurrentUser]  
               DEFAULT ( CONVERT([nvarchar](50), USER_NAME(), ( 0 )) ), 
      [LoginName] [nvarchar](50) NOT NULL 
           CONSTRAINT [DF_DDLSecurityLog_LoginName]  
               DEFAULT ( CONVERT([nvarchar](50), SUSER_SNAME(), ( 0 )) ), 
      [Username] [nvarchar](50) NOT NULL 
           CONSTRAINT [DF_DDLSecurityLog_Username]  
               DEFAULT ( CONVERT([nvarchar](50), original_login(), ( 0 )) ), 
      [EventType] [nvarchar](100) NULL, 
      [objectName] [nvarchar](100) NULL, 
      [objectType] [nvarchar](100) NULL, 
     [DatabaseName] [nvarchar](100) NULL, 
      [tsql] [nvarchar](MAX) NULL 
    ) 
ON  [PRIMARY] 
/* 
Now we will write a trigger that inserts into our security log all 
server security events. There is a bug which prevents you just  
specifying all the security events, you have to list 'em*/ 


IF EXISTS ( SELECT  * 
            FROM    sys.server_triggers 
            WHERE   name = 'trgLogServerSecurityEvents' )  
    DROP TRIGGER trgLogServerSecurityEvents ON ALL SERVER 
GO 
CREATE TRIGGER trgLogServerSecurityEvents ON ALL SERVER 
    FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN, GRANT_SERVER, DENY_SERVER, 
        REVOKE_SERVER, ALTER_AUTHORIZATION_SERVER 
AS 
    DECLARE @data XML 
    SET @data = EVENTDATA() 
    INSERT  INTO master..DDLSecurityLog 
       ( 
       EventType, 
       ObjectName, 
       ObjectType, 
       TSQL, 
      DatabaseName 
       ) 
    VALUES 
       ( 
        @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
        @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'), 
        @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)'), 
          'Server', 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') 
        )  
GO 
/* 
Now we have to create another trigger in each database which recors all 
the database security changes*/ 
USE testlogging--back to our database! 
GO 
CREATE TRIGGER trgLogDatabaseSecurityEvents ON DATABASE 
    FOR DDL_DATABASE_SECURITY_EVENTS 
AS 
    DECLARE @data XML 
    SET @data = EVENTDATA() 
    INSERT  INTO master..DDLSecurityLog 
         ( 
         EventType, 
         ObjectName, 
         ObjectType, 
        DatabaseName, 
         tsql 
         ) 
    VALUES   
         ( 
         @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
         @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'), 
         @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)'), 
         @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)'), 
         @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') 
         )  
GO 
/* now everything is in place let's test it. We will simulate an intruder's 
cunning attempt to create himself as a loging with sysAdmin rights and his 
gaining access as a database user. We could always prevent the transaction  
but that would just draw his attention to the trigger being there! 
*/  

USE [master] 
GO 
/* Heh! Heh!*/ 
CREATE LOGIN [Intruder] WITH PASSWORD= N'silly', DEFAULT_DATABASE= 
    [TestLogging], DEFAULT_LANGUAGE= [Portugu?(Brasil)], CHECK_EXPIRATION= 
    OFF, CHECK_POLICY= OFF 
GO 
EXEC master..sp_addsrvrolemember @loginame = N'Intruder', 
    @rolename = N'sysadmin' 
GO 
USE [TestLogging] 
GO 
CREATE USER [Intruder] FOR LOGIN [Intruder] 
GO 
--now we will drop the database user 
IF EXISTS ( SELECT  * 
            FROM    sys.server_principals 
            WHERE   name = N'Intruder' )  
    DROP LOGIN [Intruder] 
GO 
--and drop the login 
DROP USER [Intruder] 
GO 
/* now we can see that the whole activity has been logged. Because the 
initial CREATE  LOGIN contained a password, it has not been recorded. 
*/ 
SELECT  * FROM    master..DDLSecurityLog 

--so to end up, we just clean up! 
DROP TRIGGER trgLogDatabaseSecurityEvents ON DATABASE 
DROP TRIGGER [trgNoMonkeying] ON DATABASE 
GO 
USE master 
GO 
DROP TRIGGER trgLogServerSecurityEvents ON ALL SERVER 
DROP TABLE DDLSecurityLog  
GO 
USE [TestLogging] 
GO 
DROP TABLE NewTable 
GO 

/* 
for further reading 

   Designing DDL Triggers 
   Using the EVENTDATA Function 
   Understanding DDL Triggers 
   Implementing DDL Triggers


*/

formatted by the Simple-Talk Prettifier

This article has been viewed 58705 times.

Subject:	DDL Triggers
Posted by:	Noel Paricollo (not signed in)
Posted on:	Thursday, May 31, 2007 at 10:39 AM
Message:	Respected Mr. Phil Factor and Mrs. Robyn Page My aproach was to send an E-mail to me every time the trigger detects changes to the database so I can be aware of the change at the very moment it took place. (code below) Greetings from La Paz - Bolivia Noel Paricollo ALTER TRIGGER gCambiosEstructura ON DATABASE FOR create_table, ALTER_TABLE, Drop_Table, create_view, ALTER_view, Drop_view, create_Procedure, ALTER_Procedure, Drop_Procedure, create_index, ALTER_index, Drop_index AS DECLARE @raisedEventData XML DECLARE @Sentencia varchar(max) declare @body1 varchar(max) DECLARE @Asunto varchar(8000) DECLARE @Host varchar(100) DECLARE @recipientes varchar(4000) SET @raisedEventData = eventdata() SET @Sentencia = LEFT(cast(@raisedEventData.query ('data(/EVENT_INSTANCE/TSQLCommand/CommandText)') AS varchar(max)), 8000) SElect @Host = Host_name from sys.dm_exec_sessions WHERE session_id = cast(cast(@raisedEventData.query ('data(/EVENT_INSTANCE/SPID)') AS varchar(100)) AS int) SET @Asunto = @@servername + '-' + cast(@raisedEventData.query ('data(/EVENT_INSTANCE/EventType)') AS varchar(100)) + '-' + cast(@raisedEventData.query ('data(/EVENT_INSTANCE/ObjectName)') AS varchar(100)) + '-' + @Host set @body1 = LEFT( 'BD : ' + cast(@raisedEventData.query ('data(/EVENT_INSTANCE/DatabaseName)') AS varchar(800)) + char(13) + 'Obj : ' + cast(@raisedEventData.query ('data(/EVENT_INSTANCE/ObjectName)') AS varchar(800)) + char(13) + 'Logi: ' + cast(@raisedEventData.query ('data(/EVENT_INSTANCE/LoginName)') AS varchar(800)) + char(13) + 'User: ' + cast(@raisedEventData.query ('data(/EVENT_INSTANCE/UserName)') AS varchar(800)) + char(13) + 'SPID: ' + cast(@raisedEventData.query ('data(/EVENT_INSTANCE/SPID)') AS varchar(800)) + char(13) + 'Hora: ' + cast(@raisedEventData.query ('data(/EVENT_INSTANCE/PostTime)') AS varchar(800)) + char(13) + char(13) + @Sentencia, 8000) SET @recipientes = '[email protected]' EXEC msdb.dbo.sp_send_dbmail @recipients = @recipientes, @subject = @Asunto, @body = @body1 GO

Subject:	re: DDL Triggers
Posted by:	Robyn Page (view profile)
Posted on:	Thursday, May 31, 2007 at 3:52 PM
Message:	Noel That's great. Thanks a lot for the code and the ideas. It's nice to hear from Bolivia on this site.

Subject:	Great article indeed.
Posted by:	Mohammed Abdel-Aleem (not signed in)
Posted on:	Thursday, May 31, 2007 at 5:33 PM
Message:	Many Thanks for the valuable article and the code as well.. I was about to waste my time in creating a bunch of triggers to approach the same goal, but yours... really saved my time. Thanks again and keep going for your great articles.

Subject:	Changes to Roles
Posted by:	South Africa (not signed in)
Posted on:	Friday, June 01, 2007 at 2:30 AM
Message:	Very good article. These triggers unfortunately does not track changes to Database Roles (sp_addrolemember and sp_droprolemember). I see there is events called ADD_ROLE_MEMBER and DROP_ROLE_MEMBER, but these do not work. What would be the best way to track changes to roles?

Subject:	Re: Changes to roles
Posted by:	Robyn Page (view profile)
Posted on:	Friday, June 01, 2007 at 8:27 AM
Message:	Theoretically you could use these in event notifications to a Service Broker service. ADD_ROLE_MEMBER and DROP_ROLE_MEMBER are contained within the DDL_SERVER_SECURITY_EVENTS category. There is a bug in SQL Server which means that you get an error when you use this group name in a trigger, which is why Phil and I spelt out the events explicitly in the example above, trgLogServerSecurityEvents. What happens if you add the two event types to the procedure?

SQL Categories

Custom RSS feeds

Phil Factor

Top Rated

Most Viewed

Why Join

Subject:	SQL Server 2005 DDL Trigger
Posted by:	Anwar (view profile)
Posted on:	Saturday, June 02, 2007 at 12:47 AM
Message:	Very Good to read it.

Subject:	Changes in column names are not registered?
Posted by:	Jakob Gade (not signed in)
Posted on:	Monday, June 04, 2007 at 10:41 PM
Message:	Great article, certainly very helpful. However, a clever developer on the team quickly noticed that changes to column names are not logged to the table. Is there any way to remedy this?

Subject:	re: Changes in column names are not registered?
Posted by:	Robyn Page (view profile)
Posted on:	Tuesday, June 05, 2007 at 3:25 AM
Message:	Jakob, I think we're all struggling a bit because the books on line are not very explanatory, and we're having to experiment. The DDL_DATABASE_LEVEL_EVENTS category should include changes in column name as they are definitely DDL events. I wonder if you get an event notification from a column name-change? Congratulations to the developer on your team for noticing this.

Subject:	Question about HTML rendering
Posted by:	Anonymous (not signed in)
Posted on:	Tuesday, June 05, 2007 at 2:15 PM
Message:	I'm getting soemthing totally different for the results for the HTML rendering. -- To help, here is a better rendering of the log. We create an HTML table --and format it up prettily DECLARE @HTMLCode VARCHAR(MAX) SELECT @HTMLCode = COALESCE(@HTMLCode, ' <style type="text/css"> <!-- #changes{ border: 1px solid silver; font-family: Arial, Helvetica, sans-serif; font-size: 11px; padding: 10px 10px 10px 10px; } #changes td.date{ font-style: italic; } #changes td.tsql{ border-bottom: 1px solid silver; color: #00008B; } --> </style><table id="changes"> ') + '<tr class="recordtop"> <td class="date">' + CONVERT(CHAR(18), InsertionDate, 113) + '</td> <td class="currentuser">' + currentUser + '</td> <td class="loginname">' + LoginName + CASE WHEN loginName <> UserName THEN '(' + UserName + ')' ELSE '' END + '</td> <td class="eventtype">' + EventType + '</td> <td class="objectname">' + ObjectName + ' (' + objectType + ')' + '</td></tr> <tr class="recordbase"><td colspan="6" class="tsql"><pre>' + tsql + '</pre></td></tr> ' FROM DDLChangeLog ORDER BY insertionDate ; SELECT @HTMLCode + ' </table>'

Subject:	What if the trigger can't execute ?
Posted by:	Anonymous (not signed in)
Posted on:	Wednesday, June 06, 2007 at 6:11 AM
Message:	What happens if you store trigger output in your own database (I hate putting anything in master), and either that database or that table is not accessible for some reason ? Good article tho, I have audit requirements to track some of this stuff, so it appeared just in time. I always perfer working examples to trawling Books Online.

Subject:	Column Name Change
Posted by:	Siva Jyothi (not signed in)
Posted on:	Wednesday, June 13, 2007 at 7:19 AM
Message:	How to Change the existed column Name in the SQL 2005

Subject:	Doesn't capture disabling of itself
Posted by:	Chuck Lathrope (not signed in)
Posted on:	Thursday, June 14, 2007 at 3:48 PM
Message:	Those with proper permissions can disable the trigger, do their work and re-enable. SOX compliance might have issue with that. Any way to capture the disable event?

Subject:	HTML Table Creation
Posted by:	tgopinath (view profile)
Posted on:	Monday, June 18, 2007 at 5:33 AM
Message:	I am facing difficulty in creating HTML Table output. The results are coming in a single line. can anyone let me know why is that ....?

Subject:	Re: HTML Table creation
Posted by:	Phil Factor (view profile)
Posted on:	Thursday, June 21, 2007 at 1:37 PM
Message:	We didn't cheat. The output you see came straight from the stored procedure. Could it be that you have a CSS file somewhere that is interfering? It seems unlikely as I seem to remember we referenced all CSS in the table using the ID of "changes" but, otherwise, I'm struggling to think of a reason why it isn't working

Subject:	Capture index creation/dropping
Posted by:	Ari (not signed in)
Posted on:	Monday, July 02, 2007 at 2:39 PM
Message:	Hi, after creating the trgLogDDLEvent DDL Trigger I am unable to drop/create a new index. It is throwing an exception.

Subject:	Please sign in to comment.
Posted by:	Nadine (view profile)
Posted on:	Tuesday, July 29, 2008 at 5:20 AM
Message:	Anonymous comments have been disabled on this article due to relentless spamming. Please do continue to comment -- but you will need to sign in or join in order to do so. It jsut requires a username, email address and password. Simple-talk does not share user details with any third parties, under any circumstances.

Subject:	Using triggers to ensure duplicate usernames are not created
Posted by:	SeanKwesi (view profile)
Posted on:	Sunday, September 28, 2008 at 1:28 PM
Message:	Hi, I have an ASP.NET application connected to a database created using sql server 2005. I am trying to manage the creation of user details. I have suceeded so far but having problems ensuring duplicate usernames are not created. I have attempted using triggers but I am having problems getting the triggers to work. Below is a sample of the trigger I am trying to apply: - CREATE TRIGGER verifyscreenname ON dbo.CreateMembership FOR INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @TextBox6 varchar(50); SET @TextBox6 = Select Screen_Name From CreateMembership Where Screen_Name IN (Select CreateMembership.Screen_Name From ScreenName); SELECT Screen_Name FROM CreateMembership WHERE Screen_Name = @TextBox6; IF (@TextBox6 = Screen_Name) PRINT 'Screen Name Already Exist....'; END Please I welcome suggestions. Did read the article on the site. it was very enlightening but beyond my experience with databases.

Subject:	Update Statistics job failing
Posted by:	easysql (view profile)
Posted on:	Thursday, November 20, 2008 at 12:22 PM
Message:	When I implemented the above my update statistics job fails with the following error. Executing the query "UPDATE STATISTICS [dbo].[Country] WITH FULLSCAN " failed with the following error: "CONDITIONAL failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Subject:	Create Trigger for server restart event in SQL
Posted by:	Sathish (view profile)
Posted on:	Tuesday, July 24, 2007 at 1:11 AM
Message:	Hi.I am new to sql.Can anyone help me to create a trigger that should call a procedure when the server gets restarted.Thanks in advance for any help

Subject:	excellent article - thanks
Posted by:	Anonymous (not signed in)
Posted on:	Monday, September 10, 2007 at 4:05 AM
Message:	excellent article - thanks

Subject:	Creating Trigger in sql server
Posted by:	Anonymous (not signed in)
Posted on:	Tuesday, September 18, 2007 at 12:45 AM
Message:	Hi.I am new to sql server.Please can anyone help me how to write triggers in sql server 2005 and where exactly trigger is written.Please help me.Thanks in advance for the help.

Subject:	wonderfull article!
Posted by:	Anonymous (not signed in)
Posted on:	Friday, September 28, 2007 at 9:02 PM
Message:	well,this sems to be a good one for database level trigger.I wanna to have the same lebel of security for individual object in a databse...but it's really very nice to see .

Subject:	Great
Posted by:	Anonymous (not signed in)
Posted on:	Wednesday, November 14, 2007 at 2:43 AM
Message:	This is great article

Subject:	include clear example
Posted by:	sutrender (not signed in)
Posted on:	Tuesday, November 27, 2007 at 3:58 AM
Message:	this article is not good .my problem in not sloved give us clear article and example

Subject:	Outstanding
Posted by:	llinares (view profile)
Posted on:	Friday, September 05, 2008 at 2:22 PM
Message:	This was a very informative article. Thank you. The one thing I am struggling with is how to apply this so that the events are written to a table in a central database rather than creating a DDLChangeLog table in every database. When I try this what happens is that when a user makes a DDL change an error is raised because they do not have access to the central database where the DDLChangeLog table resides. The only way I have found around this is to set the TRUSTWORTHY property of the user database to TRUE and add the WITH EXECUTE AS SELF clause to the DDL trigger. Any suggestions would be appreciated but regardless, thank you for an excellent article.

Subject:	Ansi_Null On issues with each database on server
Posted by:	easysql (view profile)
Posted on:	Tuesday, November 18, 2008 at 1:12 PM
Message:	I tried using this piece of code which by itself is good,but implementing it gave me lots of issues.My intention was to notify the dba via email for any ddl changes that happen. here is what i did created a separate database (mondb) on the server In this database I have a table (DDLChangeLog) which is popultated by the trigger that is fired on the DDL changes in multiple databases on the server.Most of the times it works fine from start to end and even sends me email about the changes that happened with T-SQL updates in the attachments.But in some cases When this table (DDLChangeLog) is updated or inserted another trigger is fired to send an email to the DBA to notify about the changes through the sp_send_dbmail sproc in msdb. 1) Errors with the Ansi_null on,Ansi_padding_on everytime when changes are made on the dbobjects. Msg 1934, Level 16, State 1, Procedure trgLogDDLEvent, Line 6 CONDITIONAL failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query. notifications and/or xml data type methods. 2) Another issue is with the permissions to different logins which need to exist on the dbmon database because it writes data to the DLChangeLog table from different databases. 3)Then I have another error Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1 The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. This is good but I need to tweak around more to get this working . Anyone out there who were able to implement this completly.

Subject:	Please Help me..
Posted by:	Ivyflora (view profile)
Posted on:	Wednesday, February 25, 2009 at 10:49 PM
Message:	Hi Robyn Page and Phil Factor... First thanks for your helpful DDL Trigger article.. Extremely nice one...:):) i checked all the above triggers.. its working fine. now i want to change my database SQL SERVER 2005 to SQL SERVER 2000.. How do i change the following table and trigger..?? 1)DDLChangeLog 2)trgLogDDLEvent