DDL trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DDL trigger

Author

Topic

Anisha
Starting Member

10 Posts

Posted - 2008-02-26 : 07:58:53

Hello all

I'm new to SQL 2005.I need to implement DDLtrigger .I've referred to the method described in article
"Using DDL Triggers in SQL Server 2005 to Capture Schema Changes" but with the new values I want to keep the track of oldvalues ,newvalues So that I can always revert back in any case if required.
Also I should be able to track a user by desk number or IP address
as the database may have common logins.

Any suggestion is welcome.thanks in advance

Anisha

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-02-26 : 08:02:16

Can you please be more specific. What do you mean when you say "I want to keep the track of oldvalues and newchanges corresponding to it". Please give an example.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 10:34:26

quote:
Originally posted by Anisha

Hello all

I'm new to SQL 2005.I need to implement DDLtrigger .I've referred to the method described in article
"Using DDL Triggers in SQL Server 2005 to Capture Schema Changes" but with the new values I want to keep the track of oldvalues ,newvalues So that I can always revert back in any case if required.
Also I should be able to track a user by desk number or IP address
as the database may have common logins.

Any suggestion is welcome.thanks in advance

Anisha

You sound as if you are taking about DML triggers. Which new/old values are you taking about? Can you tell what you are trying to do?

Anisha
Starting Member

10 Posts

Posted - 2008-02-27 : 00:19:45

ya I meant that if someone has altered the view or stored procedure then I should be able to exactly track what were the changes made by comparing it with the older view or stored procedure,which probably is only possible if I have the older view/stored procedure along with the changed view/stored procedure.And also I want to track the user by their IP address or deskNo.
THANKS

Anisha

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-02-27 : 05:44:58

I believe you can gather only get the following information when a DDL command is executed:

<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>

Anisha
Starting Member

10 Posts

Posted - 2008-02-27 : 07:48:10

Hi all I have found solution to my problem.We can get the desk number by Host_Name function.
But with the solution provided in
"Using DDL Triggers in SQL Server 2005 to Capture Schema Changes" we can keep the track of changes made only on the present day as by default Get date() function is being used but how to keep track of all the changes made no matter what date.Please help

Anisha

Anisha
Starting Member

10 Posts

Posted - 2008-02-27 : 08:35:51

Here is what I did to meet my requirements but still I'm not able to track the old values that were altered .As I mentioned in my previous post that I want the values as it was before any alteration and the altered value to compare.The current solution gives me deskNo.thus speccifying the user who modified the database

CREATE TABLE [dbo].[ObjectLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Hostname] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Modify_Date] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TRIGGER TR_MaintainObjLog
ON DATABASE
FOR
CREATE_TABLE, ALTER_TABLE,DROP_TABLE,
CREATE_PROCEDURE,DROP_PROCEDURE,ALTER_PROCEDURE,
CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,
CREATE_QUEUE,ALTER_QUEUE,DROP_QUEUE,
CREATE_SCHEMA,ALTER_SCHEMA,DROP_SCHEMA,
CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,
CREATE_XML_SCHEMA_COLLECTION,ALTER_XML_SCHEMA_COLLECTION,DROP_XML_SCHEMA_COLLECTION

AS

SET NOCOUNT ON

DECLARE @data xml
SET @data = EVENTDATA()
insert into Vehicle.dbo.[ObjectLog](databasename, eventtype,
objectname, objecttype, sqlcommand, loginname,Hostname,Modify_Date)
select
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
Host_Name(),
Getdate()
Once again thanks Mr.Chris Rock.your work was amazing

Anyone who knows anything through which I can track what were the changes made exactly using DDL triggers ? this is somewhat
similar to INSTEAD OF and AFTER in DML trigger but unfortunately DDL triggers doesn't supports it.We 'll have to think of something else

Anisha

Anisha
Starting Member

10 Posts

Posted - 2008-03-10 : 08:14:15

Hi I implemented DDL trigger which is working fine but the pain area is I have to explicitly define all the events that I want to capture.I was looking for something thats keeps track of anything that is created,Altered,or dropped.I dont want to hard core that I want to capture the views ,tables or stored procedures.It would be gr8 if it acts as generic functions

Anisha

Anisha

Anisha
Starting Member

10 Posts

Posted - 2008-03-10 : 08:35:50

heyyyyyyyyyyyy

I got the solution to the the issue that I mentioned in the previous post.Insted of explicitly defining all the events that we want to keep track of if we mention DDL_DATABASE_LEVEL_EVENTS
then it keeps track of all the events on the database level.

That's really amazing.Try and enjoy

Anisha

pdav221
Starting Member

4 Posts

Posted - 2008-07-23 : 17:54:12

That is nice, but don't try it with "DDL_SERVER_LEVEL_EVENTS" or you'll get this error:

Msg 1082, Level 15, State 1, Procedure trDiagChgSvrLog, Line 14
"DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.

I'm running version 9.0.3257. I hope they fix it.

All I know, is not enough.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources