| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
|
|
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 |
 |
|
|
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 dropped4) 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 DamKind regards,Hans van Dam |
|
|
|
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.DoneScenario 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 table3.) Script the new table with the correct structure4.) 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
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 rotisserieAnisha |
|
|
|
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 |
|
|
|
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 helpthey are per table though and not per database._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
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 functionsAnisha |
|
|
|
Anisha
Starting Member
10 Posts |
Posted - 2008-03-10 : 08:39:09
|
| HeyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyI 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_EVENTSthen it keeps track of all the events on the database level.Try and enjoy its wonders.Its really coooooooooooolAnisha |
|
|
|
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? |
|
|
|
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. |
|
|
|
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. |
|
|
|
deviprasad
Starting Member
5 Posts |
|
|
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. |
|
|
|
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. |
|
|
|
|
Article: Using DDL Triggers in SQL Server 2005 to Capture Schema Changes