| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
yonision
Starting Member
5 Posts |
 Posted - 08/28/2007 : 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
|
 |
|
|
HanZ
Starting Member
1 Posts |
Posted - 08/28/2007 : 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 |
|
|
|
tymberwyld
Starting Member
4 Posts |
Posted - 08/29/2007 : 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
|
|
|
|
Anisha
Starting Member
India
10 Posts |
Posted - 02/26/2008 : 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 |
Edited by - Anisha on 02/26/2008 10:00:54 |
|
|
|
katehollington
Starting Member
5 Posts |
Posted - 02/27/2008 : 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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 02/27/2008 : 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 |
Edited by - spirit1 on 02/27/2008 12:19:03 |
|
|
|
Anisha
Starting Member
India
10 Posts |
Posted - 03/10/2008 : 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 |
|
|
|
Anisha
Starting Member
India
10 Posts |
Posted - 03/10/2008 : 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 |
|
|
|
ls
Starting Member
1 Posts |
Posted - 07/29/2008 : 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? |
|
|
|
platteman
Starting Member
USA
4 Posts |
Posted - 10/12/2010 : 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.
|
|
|
|
platteman
Starting Member
USA
4 Posts |
Posted - 10/12/2010 : 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. |
|
|
|
deviprasad
Starting Member
India
5 Posts |
|
| |
Topic |
|
Article: Using DDL Triggers in SQL Server 2005 to Capture Schema Changes
