Logging Database Changes
Have you ever had team members change something without telling you and not back it up, mess something up yourself, or have a boss that thinks he’s a SQL expert that sabotages things? I know I’ve had some of those problems in the past. Some of us aren’t lucky enough to have something like Visual Studio DB Pro or Tarantino implemented yet. In fact, there’s probably a large percentage of developers that just work off the production database. The team I’m on now had something in place when I got there to help with this problem. I took the general idea and made a database trigger to keep track of changes to stored procedures, functions, views, and tables. It’s no real database versioning system, but it’s a step in the right direction.
CREATE TRIGGER [LogChangeHistory] ON DATABASE FOR create_procedure, alter_procedure, drop_procedure, create_function, alter_function, drop_function, create_view, alter_view, drop_view, create_table, alter_table, drop_table AS SET NOCOUNT ON DECLARE @data XML SET @data = EVENTDATA() INSERT INTO Logging.History.ChangeLog ( DatabaseName, EventType, ObjectName, ObjectType, SqlCommand, LoginName, UpdatedOn, SqlCommandText ) VALUES ( @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), -- value is case-sensitive @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), @data.query('(/EVENT_INSTANCE/TSQLCommand)[1]'), @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'), GETDATE(), SUBSTRING(REPLACE(CONVERT(VARCHAR(MAX), @data.query('EVENT_INSTANCE/TSQLCommand/CommandText')), '
', CHAR(13) + CHAR(10)), 14, len(REPLACE(CONVERT(VARCHAR(MAX), @data.query('EVENT_INSTANCE/TSQLCommand/CommandText')), '
', CHAR(13) + CHAR(10))) - 28) ) GO
This is database level so you’ll need to put it the trigger each database. We made a separate database where we put all the changes from other databases on the server.
Blogroll
- Chris Benard
- Courtney Malone
- Dave Anderson
- Jackie Sylvester
- Jen McCain
- John Morrow
- Seth Gholson
- Stephen Culver
Archives
- August 2010 (3)
Categories
Twitter - @robotbabytiger
- Hyundai, as in "OMG the back passenger side window just shattered randomly when I rolled it down in my Hyundai"
- My feelings for DynamicPDF have shifted from begrudging tolerance to a rather strong dislike.
- I just unfollowed a bunch of people. Maybe this will make twitter more usable.
- Does anyone still play Lord of the Rings online? I'm thinking of starting up again.
- Getting oil changed. The computer at the register is running ubuntu.
August 11th, 2010 - 21:32
dear chris,
i did not know you had a blog.
sincerely,
vanessa
August 12th, 2010 - 10:39
Sorry. I thought I told you. You know now.