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
 Site Related Forums
 Article Discussion
 Article: Using DDL Triggers in SQL Server 2005 to Capture Schema Changes

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-08-13 : 07:05:12
This article shows how to use DDL triggers to capture schema changes. This solutions works a little differently than most DDL triggers examples. This script captures the old object that was replaced.

Read Using DDL Triggers in SQL Server 2005 to Capture Schema Changes

yonision
Starting Member

5 Posts

Posted - 2007-08-28 : 14:28:44
Great article! Short and to the point. I also wanted to point out that if you want to achieve exactly the same thing, but also with elaborate reports and automatic push to SourceSafe, try out:
http://www.nobhillsoft.com/Randolph.aspx

Go to Top of Page

HanZ
Starting Member

1 Post

Posted - 2007-08-28 : 18:59:42
quote:
Originally posted by AskSQLTeam

This article shows how to use DDL triggers to capture schema changes. This solutions works a little differently than most DDL triggers examples. This script captures the old object that was replaced.

Read <a href="/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes">Using DDL Triggers in SQL Server 2005 to Capture Schema Changes</a>



Hi there.

I already have some experience with this functionality. I even have a working system already but there's one problem...

Some table alterations from whitin SSMS are not captured in a usable fashion. For example, when you add a column to a table somewhere in the middle:
1) a temp table is created which includes te new column;
2) the temp table is populated with the contents of the old table, exept for the added column of course;
3) the old table is dropped
4) the temp table is renamed to the old table.

The problem here is that the last logged action for that particular table is the drop of that table while there's no record of the 'rename', so if you rely on the log, the table does not exist anymore while the truth is that it does exist with one column more than before.

I looked for a solution where the original sp_rename could remain unchanged but I did not succeed.

Does anyone have a solution for that?

Kind regards,

Hans van Dam


Kind regards,

Hans van Dam
Go to Top of Page

tymberwyld
Starting Member

4 Posts

Posted - 2007-08-29 : 07:13:59
This is a good article and I'm going to implement it even though I use "Database Projects" that are provided with Visual Studio 2003 / 2005. The Database Projects are really nice since they can be integrated into Source Safe, plus I can structure it any way I want. It also integrates directly into a database connection so I can execute scripts one by one. I also create a batch file which scripts everything at once for a complete solution. I've never had a problem with these except that it's a little tedious to get setup, but once that's done it's just a matter of maintenance. I think I'll also setup your solution just in case I miss something.

Hans, one thing you can try is to reverse the order in which things happen.

Scenario 1:
1.) If the Table does not exist, create it WITHOUT the "tmp_" prefix.
Done

Scenario 2:
1.) If the table exists, but the schema is incorrect (i.e. a Column needs inserted), select all the data into a temporary table (Select Into tmp_MyTable From MyTable...)
2.) Drop the old table
3.) Script the new table with the correct structure
4.) Insert all the Data back into the new table (minus the new columns of course).

Ex:
I just did this really quick so it's just a rough draft. Of course this forum is going to strip all the formatting so it's going to look ugly.

If (Object_ID('[dbo].[Applications]') Is Null)
CREATE TABLE dbo.Applications (
ID uniqueidentifier NOT NULL ROWGUIDCOL,
Name varchar(50) NOT NULL,
Version varchar(15) NULL,
PasswordExpiration tinyint NULL,
PasswordAudits tinyint NULL,
PasswordAttempts tinyint NULL,
Obsolete bit NULL, -- Added in Version 1.0.0.1
CreatedBy uniqueidentifier NULL,
CreatedDate datetime NULL,
ModifiedBy uniqueidentifier NULL,
ModifiedDate datetime NULL
) ON [PRIMARY]
Else If Not Exists(Select Null From dbo.SysColumns Where id = Object_ID('[dbo].[Applications]') And Name = 'Obsolete')
BEGIN
If Exists(Select Null From dbo.Applications)
Select Into dbo.tmp_Applications From dbo.Applications

-- Drop any Foreign Keys and the Table
ALTER TABLE dbo.Members DROP CONSTRAINT FK_ApplicationMembers_Applications
DROP TABLE dbo.Applications

-- Create the Table
CREATE TABLE dbo.Applications (
ID uniqueidentifier NOT NULL ROWGUIDCOL,
Name varchar(50) NOT NULL,
Version varchar(15) NULL,
PasswordExpiration tinyint NULL,
PasswordAudits tinyint NULL,
PasswordAttempts tinyint NULL,
Obsolete bit NULL, -- Added in Version 1.0.0.1
CreatedBy uniqueidentifier NULL,
CreatedDate datetime NULL,
ModifiedBy uniqueidentifier NULL,
ModifiedDate datetime NULL
) ON [PRIMARY]

If (Object_ID('[dbo].[tmp_Applications]') Is Not Null)
EXEC('INSERT INTO dbo.Applications
(ID, Name, Version, PasswordExpiration, PasswordAudits, PasswordAttempts,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
SELECT ID, Name, Version, PasswordExpiration, PasswordAudits, PasswordAttempts,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate
FROM dbo.tmp_Applications WITH (HOLDLOCK TABLOCKX)')
END
Go to Top of Page

Anisha
Starting Member

10 Posts

Posted - 2008-02-26 : 09:39:17
This was tooooo good,very simple and very efficient but how to keep track of each user according to IP address or desk No as different user may have same loginName and password for the database. .And how to retain the older values if it has been modified.I want something similar to this that is very easy to understand.

Thanks Mr.Chris Rock .It is really a rotisserie

Anisha
Go to Top of Page

katehollington
Starting Member

5 Posts

Posted - 2008-02-27 : 12:08:45
This article was great - thanks.
Was wondering though, if there was a similar function that would capture any UPDATE, DELETE and INSERT statements that are run against a database?
Thanks
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-27 : 12:18:35
those are called DML triggers
look for after and instead of triggers in BOL = Books Online = SQL Server help
they are per table though and not per database.


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

Anisha
Starting Member

10 Posts

Posted - 2008-03-10 : 08:06:04
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
Go to Top of Page

Anisha
Starting Member

10 Posts

Posted - 2008-03-10 : 08:39:09
Heyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

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.Try and enjoy its wonders.
Its really cooooooooooool


Anisha
Go to Top of Page

ls
Starting Member

1 Post

Posted - 2008-07-29 : 09:36:55
I'm sure I have made some basic error but when I tried implementing this process I ran into a problem when using a central database to capture the data. When a user in the db_owner role of a user database tried to create a table, the operation failed because he had no rights to the central database. If I added him to the central database in the db_datawriter role, then it worked. Any suggestions?
Go to Top of Page

platteman
Starting Member

4 Posts

Posted - 2010-10-12 : 14:13:23
How can you add a DDL trigger to every database? I have tried all the usual hacks and I have come up blank.

Go to Top of Page

platteman
Starting Member

4 Posts

Posted - 2010-10-12 : 22:59:47
quote:
Originally posted by platteman

How can you add a DDL trigger to every database? I have tried all the usual hacks and I have come up blank.





Just thought of something I need to test. How about using policy management to check for the trigger and have it push it out. Just thinking out loud but will have to test.
Go to Top of Page

deviprasad
Starting Member

5 Posts

Posted - 2011-06-28 : 09:14:15
DDL.

The following link explains about DDL Statements SQL SERVER DDL STATEMENTS

Devi Prasad.
Learn SQL Server


Thanks,
Devi Prasad
Learn SQL Server
Go to Top of Page

denialparl
Starting Member

5 Posts

Posted - 2014-07-17 : 06:35:36
To track all the users activities in SQL server database, You can have a look at this automated utility available at (http://www.lepide.com/sql-server-audit/) and would be a perfect approach in your environment. It audit all the changes made in SQL database at granular level and provide the captured data with real time monitoring. It provide the graphical overview of important changes and an overview of audited objects. Further, you can create the filter to track only the required objects, operations and users as according to your requirement.
Go to Top of Page

Daniel Wai Kian Larb
Starting Member

1 Post

Posted - 2014-11-04 : 21:15:41
Can someone tell me what is the use of storing schema name in database tracking? Thanks.
Go to Top of Page
   

- Advertisement -