August 23, 2011 at 12:30 pm
I need to create an audit trail that easily identifies changes to a record. Part of the purpose of this is to show users what information was actually changed with an "Old Value" and "New Value" so that they can determine if the change impacts them. The DB that I'm working with is the backend for an ASP.NET application. The eventual goal is to be able to give the users a page that shows them changes that have happened to records that are of interest to them.
I've figured out the design of the table that I would like to use to capture the information, however I realized that it would effectively be taking a single row of data from the actual table and breaking it up into multiple rows in the table for the audit log. There's be one row in the audit log for each COLUMN in the parent table. Obviously, once again, my need far exceeds my capabilities.
Once of the purposes of tmp_AuditLog is to take advantage of the ability to capture which ASP or SQLServer object actually changed the data to help track down problems or research issues which is why appropriate columns are included.
The specific questions...
1) Does this make sense in terms of an approach?
2) How would I take a single record and transform it from being a single record to multiple records one for each change that was actually made? Ideally, I'd like something that is easily adapted to multiple tables with little modification.
Here is my initial concept for the audit log table (subject to change)
CREATE TABLE [dbo].[tmp_AuditLog](
[Id] [int] IDENTITY(1000,1) NOT NULL,
[Action] [varchar](10) NOT NULL,
[ShowInformationElement] [varchar](25) NULL,
[SQLServerTable] [varchar](75) NULL,
[SQLServerColumn] [varchar](100) NULL,
[OldValue] [varchar](255) NULL,
[NewValue] [varchar](255) NULL,
[WindowsUserId] [varchar](28) NULL,
[Batch] [int] NULL,
[DateTime] [datetime] NULL,
[ASPObject] [varchar](128) NULL,
[SQLServerObject] [varchar](128) NULL,
[Comment] [varchar](255) NULL,
CONSTRAINT [PK_tmp_AuditLog] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Here's a sample parent table and data if needed
CREATE TABLE [dbo].[tmpDemo](
[ProjectNumber] [varchar](8) NOT NULL,
[Office] [smallint] NOT NULL,
[ProjectType] [char](3) NOT NULL,
[NameLong] [varchar](125) NULL,
[NameShort] [varchar](15) NULL,
[Status] [varchar](15) NULL,
[EntryUserId] [varchar](15) NULL,
[EntryDateTime] [datetime] NULL,
[ModifiedUserId] [varchar](15) NULL,
[ModifiedDateTime] [datetime] NULL,
CONSTRAINT [PK_ProjectNumber] PRIMARY KEY CLUSTERED
(
[ProjectNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO tmpDemo (ProjectNumber, Office, ProjectType, NameLong, NameShort, Status)
VALUES (000111, 110, 'CAP', '3 Position Origin', '3PO', 'Pending')
GO
INSERT INTO tmpDemo (ProjectNumber, Office, ProjectType, NameLong, NameShort, Status)
VALUES (000221, 200, 'EXP', 'Room Assignment Search', 'RA Search', 'Design')
GO
INSERT INTO tmpDemo (ProjectNumber, Office, ProjectType, NameLong, NameShort, Status)
VALUES (005511, 300, 'CAP', '22 By 7 Uptime', '227', 'Approved')
GO
August 23, 2011 at 1:16 pm
Just my 2¢ but I think you will find a lot more issues down the road trying to parse this out column by column. It is incredibly difficult to put together a query that can show what the value of the record was at a specific point in time. Let's say you have 100 changes in your table across 13 columns. Now what was the value of that record on March 12, 2011? It is REALLY hard to put that together on the fly. It is a lot easier (and usually faster performance wise) to just audit the whole record. Then you can easily look at the value historically. Yes it can make a lot of redundant data but in the end will make your life a million times easier.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2011 at 1:34 pm
Sean Lange (8/23/2011)
Just my 2¢ but I think you will find a lot more issues down the road trying to parse this out column by column. It is incredibly difficult to put together a query that can show what the value of the record was at a specific point in time. Let's say you have 100 changes in your table across 13 columns. Now what was the value of that record on March 12, 2011? It is REALLY hard to put that together on the fly. It is a lot easier (and usually faster performance wise) to just audit the whole record. Then you can easily look at the value historically. Yes it can make a lot of redundant data but in the end will make your life a million times easier.
That did occur to me, the catch is that end users do have a specific need to know what was changed. Each 'project' has muliple subcomponents which are represented by child tables (staffing, dates, facilities, etc.) which if something changes, the end users need to be able to easily identify WHAT specifically was changed. Right now, they're having to compare report 1 to report 2 which is proving to be tedious as it could be a very, very minor change.
I'm now leaning toward a table that captures the general change...
[Id] [Date],[Action],[ElementChanged], etc., etc.
1000, 5/1/11,Update,Facility Information
Copies of the record from the [inserted] and [deleted] recordsets would then be placed into a table with the same structure as the parent but with an additional column that captures [Id] from the Change Header table above. Thus you would have the specific changes available plus meta information. A comparison when the information is rendered on the ASP.NET side would be able to identify the specific changes that were made.
August 23, 2011 at 1:40 pm
I think your audit table approach will be easier to code and maintain and it will be light years faster to produce the history. Breaking it apart column by column is really painful. You just about have to do an if exists in audit table then do subselect else just select for each and every column. I worked on a system once that used the style of auditing you are referring to and nobody could use the history because it was just too slow. The audit table approach is great for analysis because you can view the entire history at once and look at changes over time. Sounds like you have enough knowledge to put that all together but let us know if you need help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2011 at 1:53 pm
I know that logic on the ASP.NET side can easily remove the data that remained the same, but is it possible for SQLServer to join the two records and the 'hide' the data that wasn't changed? Something like
SELECT
CASE t1.ProjectName
WHEN t2.ProjectName THEN ''
ELSE 'Old Value: ' + t1.ProjectName + ' ' + 'New Value: ' + t2.ProjectName AS ProjectName
Pardon any syntax errors, I'm thinking off the top of my head.
August 23, 2011 at 2:04 pm
Yeah that looks like a close enough skeleton that would work. However, I would suggest putting the presentation on the front end. Otherwise you will have to look at each column and put a '' when there is not a change. The other compelling reason is things like dates. If you want an '' you will have to convert your datetime fields to varchar or you will get 1/1/1900 and again this is presentation not data retrieval. The .NET framework can handle the presentation a lot more quickly and gracefully than sql can.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2011 at 2:19 pm
Sean Lange (8/23/2011)
Yeah that looks like a close enough skeleton that would work. However, I would suggest putting the presentation on the front end. Otherwise you will have to look at each column and put a '' when there is not a change. The other compelling reason is things like dates. If you want an '' you will have to convert your datetime fields to varchar or you will get 1/1/1900 and again this is presentation not data retrieval. The .NET framework can handle the presentation a lot more quickly and gracefully than sql can.
I just sketched out some ideas for the presentation side of it which in doing so helped me to start thinking about the logic behind it. It'll be an interesting exercise as I've played around with the idea of using an asp:repeater to display record information vertically instead of horizontally which is the norm.
August 23, 2011 at 11:48 pm
Hi David,
We did the same type of auditing on a current project and it works great 🙂
We wrote a stored proc to generate triggers for all the tables requiring auditing, and I would suggest this, thus you can just regenerate your auditing functionality when ever you made changes on your table structures, saves you time at the end and keeps a uniform structure to your auditing code for debugging 😛
Some tips, to keep your audit data to a min, just make one entry on insert, you will know who made the insert, and only log updated data from then, because this type of auditing will see your data grow at a phenomenal pace.
Some info from a noob, hope it helps 🙂
Kind regards
August 24, 2011 at 6:30 am
ajsnyman (8/23/2011)
Hi David,We did the same type of auditing on a current project and it works great 🙂
We wrote a stored proc to generate triggers for all the tables requiring auditing, and I would suggest this, thus you can just regenerate your auditing functionality when ever you made changes on your table structures, saves you time at the end and keeps a uniform structure to your auditing code for debugging 😛
Some tips, to keep your audit data to a min, just make one entry on insert, you will know who made the insert, and only log updated data from then, because this type of auditing will see your data grow at a phenomenal pace.
Some info from a noob, hope it helps 🙂
Kind regards
Can you post the sp that you used to create the triggers?
There's a great deal of downstream work that's occurs from a single change so unfortunately the users need to be able to easily see what exactly was changed. Fortunately, once a project is complete all of the change information can be immediately deleted.
August 24, 2011 at 7:53 am
Sorry man, it will reveal too much of our internal structure, which is commercial.
The basic structure:
- Create a cursor containing all the tables you want to create the trigger for
- Create a cursor containing all the columns per table
- Create a varchar with the basic structure of your trigger and replace in the table name where required
- Create a comparison clause and insert for each column, this will need to be specified for each SQL data type that you use.
- Insert these lines into the trigger structure
- Drop and create the trigger via exec @trigger
Hope this helps a bit 🙂
August 24, 2011 at 8:07 am
David, depending on the number of tables you may find that just coding them is easier. The amount of effort to create this type of stored proc is pretty large. The basic layout above looks pretty good except it forgot that you will also have to change the structure of your audit tables to match the base tables. This could get kind of nasty because I would assume you have some additional data in your audit tables not present in the base tables. Also this stored proc is only really useful if you are making a lot of changes to the ddl frequently. If you have a large number of these triggers this proc could be REALLY slow. Unless you have a really large number of base tables you want to audit and you change the ddl frequently I would suggest to just write the triggers manually.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 24, 2011 at 8:24 am
ajsnyman (8/24/2011)
Sorry man, it will reveal too much of our internal structure, which is commercial.The basic structure:
- Create a cursor containing all the tables you want to create the trigger for
- Create a cursor containing all the columns per table
- Create a varchar with the basic structure of your trigger and replace in the table name where required
- Create a comparison clause and insert for each column, this will need to be specified for each SQL data type that you use.
- Insert these lines into the trigger structure
- Drop and create the trigger via exec @trigger
Hope this helps a bit 🙂
...and you're thinking that the table that I posted was copied exactly from the DB? I fully understand. I was curious to see how you'd pull something like that off. It did occur to me that due to some pre-existing triggers that it'd have to be done manually anyways. Quite fortunately its going to be implemented piece meal so adding them individually won't be that big of an issue. The tables will also be similarly named as in ProjectHeaders and ProjectHeaders_Audit so that hopefully any future DB that makes a change to one will realize that it also has to be made to the child table. Of course, that now has me wondering if you couldn't create a stored procedure through which table changes are made which would allow both tables to always be kept in sync in terms of design.
August 24, 2011 at 8:37 am
Would this be a case where the native Change Data Capture in 2008 could help?
http://msdn.microsoft.com/en-us/library/cc280462.aspx"> http://msdn.microsoft.com/en-us/library/cc280462.aspx
August 24, 2011 at 9:39 am
david.holley (8/24/2011)
...and you're thinking that the table that I posted was copied exactly from the DB? I fully understand. I was curious to see how you'd pull something like that off. It did occur to me that due to some pre-existing triggers that it'd have to be done manually anyways. Quite fortunately its going to be implemented piece meal so adding them individually won't be that big of an issue. The tables will also be similarly named as in ProjectHeaders and ProjectHeaders_Audit so that hopefully any future DB that makes a change to one will realize that it also has to be made to the child table. Of course, that now has me wondering if you couldn't create a stored procedure through which table changes are made which would allow both tables to always be kept in sync in terms of design.
I could see it's an example :hehe:
Ye, it's a lot of work at first, but we can adapt it for any future project.
To replicate the structure would be cool, but scary, every time tables just change automatically your .net project suddenly stop working, it'll be DBA vs Developer :w00t:
August 24, 2011 at 9:49 am
ajsnyman (8/24/2011)
david.holley (8/24/2011)
...and you're thinking that the table that I posted was copied exactly from the DB? I fully understand. I was curious to see how you'd pull something like that off. It did occur to me that due to some pre-existing triggers that it'd have to be done manually anyways. Quite fortunately its going to be implemented piece meal so adding them individually won't be that big of an issue. The tables will also be similarly named as in ProjectHeaders and ProjectHeaders_Audit so that hopefully any future DB that makes a change to one will realize that it also has to be made to the child table. Of course, that now has me wondering if you couldn't create a stored procedure through which table changes are made which would allow both tables to always be kept in sync in terms of design.I could see it's an example :hehe:
Ye, it's a lot of work at first, but we can adapt it for any future project.
To replicate the structure would be cool, but scary, every time tables just change automatically your .net project suddenly stop working, it'll be DBA vs Developer :w00t:
I am one and the same so as long as the voices in my head are preoccupied with which cigar I'm going to have tonight, I should be fine. Plus, most (if not all) of my data access is through views and stored procedures to begin with.
My thought was to create a stored procedure that handles changing the structure of the table so that it can change both at the same time encapsulating the neccessary commands. I used the basic concept on an Access project a year ago where pass-through-queries were executed via a SUB that created the pass-through if it didn't already exist.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply