Monitoring Changes in Your Database Using DDL Triggers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by David Dye » Monitoring Changes in Your Database Using DDL...

45 posts, Page 2 of 5

««1 2 3 4 5 »»»

Monitoring Changes in Your Database Using DDL Triggers

Rate Topic

Display Mode

Topic Options

Author

Message

Steven Mann

Posted Thursday, October 02, 2008 7:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, July 30, 2010 9:23 AM
Points: 9, Visits: 20

Fantastic! I appreciate your direct answer to my questions. :)

Post #579635

Jorge Novo-@ETLDEVDBA

Posted Thursday, October 02, 2008 7:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 137, Visits: 250

We used a similar aproach,however, most of the time whe want the trigger to do as little and as fast as possible, so we move the data straight into an XML field and created a view to parse the xml which give us a better performance.

Post #579637

Harry Drenth

Posted Thursday, October 02, 2008 7:47 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 09, 2013 3:56 AM
Points: 16, Visits: 146

I'm using it this way on SQL 2005 Databases for version control:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DatabaseDDLLogging](
[id] [uniqueidentifier] NOT NULL,
[EventType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostTime] [datetime] NULL,
[SPID] [int] NULL,
[ServerName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoginName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DatabaseName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SchemaName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CommandText] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_DatabaseLogging] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [trig_event_data]
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
CREATE_VIEW, DROP_VIEW, ALTER_VIEW,
CREATE_INDEX, DROP_INDEX, ALTER_INDEX,
CREATE_FUNCTION, DROP_FUNCTION, ALTER_FUNCTION,
CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE

AS

DECLARE @DATA XML
SET @DATA = EVENTDATA()

INSERT INTO DATABASEDDLLOGGING (EVENTTYPE,
POSTTIME,
SPID,
SERVERNAME,
LOGINNAME,
USERNAME,
DATABASENAME,
SCHEMANAME,
OBJECTNAME,
OBJECTTYPE,
COMMANDTEXT
) VALUES
( @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime'),
@data.value('(/EVENT_INSTANCE/SPID)[1]','int'),
@data.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
)

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trig_event_data] ON DATABASE

Post #579649

cm62597

Posted Thursday, October 02, 2008 7:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 24, 2012 1:06 PM
Points: 48, Visits: 167

Do I have to modify this trigger to work with my database? When I try to run the trigger in SQL2000 it blows syntax errors? Can anyone help?

Sorry I am new to the database world Hehe

This is my errors.

Server: Msg 156, Level 15, State 1, Procedure Audit, Line 1
Incorrect syntax near the keyword 'DATABASE'.
Server: Msg 195, Level 15, State 1, Procedure Audit, Line 10
'EVENTDATA' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Procedure Audit, Line 11
Line 11: Incorrect syntax near '.'.
Server: Msg 170, Level 15, State 1, Procedure Audit, Line 13
Line 13: Incorrect syntax near '.'.
Server: Msg 170, Level 15, State 1, Procedure Audit, Line 14
Line 14: Incorrect syntax near '.'.
Server: Msg 170, Level 15, State 1, Procedure Audit, Line 15
Line 15: Incorrect syntax near '.'.

Post #579651

Harry Drenth

Posted Thursday, October 02, 2008 7:52 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 09, 2013 3:56 AM
Points: 16, Visits: 146

it only works for sql2005 or higher

Post #579653

yu.he

Posted Thursday, October 02, 2008 8:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 7:10 AM
Points: 9, Visits: 49

I am using SQL Server 2000, and the script for creating the Trigger doesn't work. I got "Incorrect syntax near the keyword 'Database'.". Is there any way I can apply this logic to SQL Server 2000?

Thanks.

Post #579682

Harry Drenth

Posted Thursday, October 02, 2008 8:32 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 09, 2013 3:56 AM
Points: 16, Visits: 146

don't think so

Post #579685

David Dye

Posted Thursday, October 02, 2008 8:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 4:35 AM
Points: 6, Visits: 229

Unofrtunately DDL triggers are only available in SQL 2005 or higher.

Post #579686

Carla Wilson-484785

Posted Thursday, October 02, 2008 9:50 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:15 AM
Points: 1,352, Visits: 1,738

HD,
I really like the extra columns you have included for Database name, Object name, etc.
Thanks for sharing!

Post #579754

Timothy Lenz

Posted Thursday, October 02, 2008 11:58 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 01, 2012 10:01 PM
Points: 14, Visits: 71

Hi David,

I like the idea of the article but had a problem when I put it on our developer machine

after running your test the results I got from other developers came out like this:

ID Command PostTime HostName LoginName
1 UPDATE STATISTICS tbluser 2008-10-02T08:51:07.563 NSSLT5 NSSDEVSQL\rcsnslenzti
2 CREATE TABLE dbo.Test(col INT) 2008-10-02T08:51:07.640 NSSLT5 NSSDEVSQL\rcsnslenzti
3 DROP TABLE dbo.Test 2008-10-02T08:51:07.670 NSSLT5 NSSDEVSQL\rcsnslenzti
4 --ENCRYPTED-- 2008-10-02T09:49:14.767 NSSDEVWEB NSSDEVSQL\RCSNETUSER
5 --ENCRYPTED-- 2008-10-02T09:49:14.827 NSSDEVWEB NSSDEVSQL\RCSNETUSER
6 --ENCRYPTED-- 2008-10-02T09:49:14.843 NSSDEVWEB NSSDEVSQL\RCSNETUSER
7 --ENCRYPTED-- 2008-10-02T09:49:14.877 NSSDEVWEB NSSDEVSQL\RCSNETUSER
8 ALTER TABLE dbo.tblVIFixes ADD test nchar(10) NULL 2008-10-02T11:47:51.767 NSSLT5 NSSDEVSQL\rcsnslenzti

I ran a check by opening a table and adding a column test and then ran the query again. This gave me the newest line 8

My question is what would give the --ENCRYPTED-- response?

I hope to get this working to track changes undocumented by developers!

Thanks,

Tim

Post #579863

« Prev Topic | Next Topic »

45 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions