Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring Changes in Your Database Using DDL Triggers


Monitoring Changes in Your Database Using DDL Triggers

Author
Message
Steven Mann
Steven Mann
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 20
Fantastic! I appreciate your direct answer to my questions. Smile
Jorge Novo-@ETLDEVDBA
Jorge Novo-@ETLDEVDBA
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 251
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.
Harry Drenth
Harry Drenth
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 181
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
cm62597
cm62597
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 256
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 worldHehe

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 '.'.
Harry Drenth
Harry Drenth
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 181
it only works for sql2005 or higher
yu.he
yu.he
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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.
Harry Drenth
Harry Drenth
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 181
don't think so
David Dye
David Dye
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 308
Unofrtunately DDL triggers are only available in SQL 2005 or higher.
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1745 Visits: 1949
HD,
I really like the extra columns you have included for Database name, Object name, etc.
Thanks for sharing!
Timothy Lenz
Timothy Lenz
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search