Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Monitoring Changes in Your Database Using DDL Triggers Expand / Collapse
Author
Message
Posted Thursday, October 2, 2008 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, October 2, 2008 7:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, 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.
Post #579637
Posted Thursday, October 2, 2008 7:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:11 AM
Points: 16, Visits: 164
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
Posted Thursday, October 2, 2008 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:14 AM
Points: 48, Visits: 239
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

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
Posted Thursday, October 2, 2008 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:11 AM
Points: 16, Visits: 164
it only works for sql2005 or higher
Post #579653
Posted Thursday, October 2, 2008 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, October 2, 2008 8:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:11 AM
Points: 16, Visits: 164
don't think so
Post #579685
Posted Thursday, October 2, 2008 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:12 AM
Points: 7, Visits: 247
Unofrtunately DDL triggers are only available in SQL 2005 or higher.
Post #579686
Posted Thursday, October 2, 2008 9:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 1,553, Visits: 1,846
HD,
I really like the extra columns you have included for Database name, Object name, etc.
Thanks for sharing!
Post #579754
Posted Thursday, October 2, 2008 11:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 1, 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 »

Add to briefcase ««12345»»»

Permissions Expand / Collapse