September 6, 2011 at 11:29 am
I'm working with a SQLServer DB that is the backend to an ASP.NET application. I am in the process of implementing functionality within SQLServer to begin capturing record changes for audit/loggin purposes. I have in place triggers that run AFTER INSERT/UPDATE/DELETE that successfully copy the changes into a audit tables.
I would like to be able to relate the changes together to make it easy for the end user to identify what specifically was changed. Does SQLServer generate some sort of transaction tracking number when a user performs an action that adds/edits/deletes a record?
September 6, 2011 at 11:38 am
Just out of curiosity, since you using SQL Server 2008, why did you not look for the tools provided be them? CT, CDC and SQL Audit? They have build in functions and viewers depending on the tool.
When you use trigger, you might end up with unwanted issues.
-Roy
September 6, 2011 at 12:02 pm
I have similar requirement before in my environment and I tried setting up CDC as I'm having SQL Server 2008 r2 but din't work really as it is a subscriber database in replication due to some other reasons and then chose DML triggers to do it. So I have put my work around here [/url]
Hopefully helps!
September 6, 2011 at 12:05 pm
These are the exact challenges I was suggesting you would run into in your original thread about these auditing triggers. It is challenging to put these records back together so the end user has a visual representation of what happened and when. This is why it usually a lot simpler to store the whole row in your audit table. It does take a bit more storage space but it is a zillion times easier to show users what happened to their data. 😉
_______________________________________________________________
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/
September 6, 2011 at 12:07 pm
Roy Ernest (9/6/2011)
Just out of curiosity, since you using SQL Server 2008, why did you not look for the tools provided be them? CT, CDC and SQL Audit? They have build in functions and viewers depending on the tool.When you use trigger, you might end up with unwanted issues.
Because I have no idea of what CT, CDC and SQL Audit actually are and obviously not how to use them. This was a spin off from another thread in which the general decision was that triggers were the best route to go given my specific requirements.
September 6, 2011 at 12:10 pm
One more thing to keep in mind is that if you are using datetime to find what are the changes done, then there is a chance that you might miss some changes in between.
-Roy
September 6, 2011 at 12:13 pm
Sean Lange (9/6/2011)
These are the exact challenges I was suggesting you would run into in your original thread about these auditing triggers. It is challenging to put these records back together so the end user has a visual representation of what happened and when. This is why it usually a lot simpler to store the whole row in your audit table. It does take a bit more storage space but it is a zillion times easier to show users what happened to their data. 😉
Storing the entire row is the direction that I'm going. INSERT and DELETE are simple since there's only one record to deal with (inserted/deleted), however since an UPDATE creates a record in both the 'inserted' and 'deleted' recordsets, I wanted to capture both as I was thinking that doing so at the same time via the trigger would make it easier to see what specifically was changed.
September 6, 2011 at 12:15 pm
There are lots of articles on Change tracking and Change data control. You would be able to chose what technology you want to use based on the business needs.
The over head is pretty small. In the case of CT, it will use an additional 72 bytes of data. But that is very low.
-Roy
September 6, 2011 at 12:19 pm
Roy Ernest (9/6/2011)
There are lots of articles on Change tracking and Change data control. You would be able to chose what technology you want to use based on the business needs.The over head is pretty small. In the case of CT, it will use an additional 72 bytes of data. But that is very low.
Its SQL Server Standard Edition.
So back to the original question...
September 6, 2011 at 12:26 pm
david.holley (9/6/2011)
Sean Lange (9/6/2011)
These are the exact challenges I was suggesting you would run into in your original thread about these auditing triggers. It is challenging to put these records back together so the end user has a visual representation of what happened and when. This is why it usually a lot simpler to store the whole row in your audit table. It does take a bit more storage space but it is a zillion times easier to show users what happened to their data. 😉Storing the entire row is the direction that I'm going. INSERT and DELETE are simple since there's only one record to deal with (inserted/deleted), however since an UPDATE creates a record in both the 'inserted' and 'deleted' recordsets, I wanted to capture both as I was thinking that doing so at the same time via the trigger would make it easier to see what specifically was changed.
Honestly if you just capture the changed data you should be fine. The inserted table contains what is current at the time. Since that is "rolling window" just capturing what the data was before update you can build the whole pretty easily.
I don't remember what your tables and such look like but here is an example. In this example the audit table has a single field not in the base table called SysDateChanged.
select *, null as SysDateChanged from BaseTable
where BaseTableID = @ProjectNum
union all
select * from BaseTableAudit where BaseTableID = @ProjectNum
order by SysDateChanged
Simple query and you have the entire history from the audit table and the current values ordered by the date the row was changed. Drop this into a datagrid and the user can see exactly what columns changed and when. Eliminates the need to capture two sets of data for each change. Just leave the current data in the base table. Does that make sense?
_______________________________________________________________
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/
September 6, 2011 at 12:33 pm
Sean Lange (9/6/2011)
david.holley (9/6/2011)
Sean Lange (9/6/2011)
These are the exact challenges I was suggesting you would run into in your original thread about these auditing triggers. It is challenging to put these records back together so the end user has a visual representation of what happened and when. This is why it usually a lot simpler to store the whole row in your audit table. It does take a bit more storage space but it is a zillion times easier to show users what happened to their data. 😉Storing the entire row is the direction that I'm going. INSERT and DELETE are simple since there's only one record to deal with (inserted/deleted), however since an UPDATE creates a record in both the 'inserted' and 'deleted' recordsets, I wanted to capture both as I was thinking that doing so at the same time via the trigger would make it easier to see what specifically was changed.
Honestly if you just capture the changed data you should be fine. The inserted table contains what is current at the time. Since that is "rolling window" just capturing what the data was before update you can build the whole pretty easily.
I don't remember what your tables and such look like but here is an example. In this example the audit table has a single field not in the base table called SysDateChanged.
select *, null as SysDateChanged from BaseTable
where BaseTableID = @ProjectNum
union all
select * from BaseTableAudit where BaseTableID = @ProjectNum
order by SysDateChanged
Simple query and you have the entire history from the audit table and the current values ordered by the date the row was changed. Drop this into a datagrid and the user can see exactly what columns changed and when. Eliminates the need to capture two sets of data for each change. Just leave the current data in the base table. Does that make sense?
So then I for any change, it'd just be a matter of doing a SELECT on the table with the next lowest primary key than the record being viewed as in
PK
1
2
3
4
5
If the record with PK of 4 is being viewed, select the record with PK of 3 to see the state of the record before the change...
September 6, 2011 at 12:43 pm
there is a project on codeplex i briefcased that allows you to add a CDC equivilent to plain old SQL 2005/2008, stadnard editions , so you don't need Enterprise Edition.
I haven't played with it yet, but you could look at that as well:
http://standardeditioncdc.codeplex.com/
--tip of the hat for the source of for the info to Dave23 in this post:
http://qa.sqlservercentral.com/Forums/Topic1158576-391-1.aspx
Lowell
September 6, 2011 at 1:03 pm
So then I for any change, it'd just be a matter of doing a SELECT on the table with the next lowest primary key than the record being viewed as in
PK
1
2
3
4
5
If the record with PK of 4 is being viewed, select the record with PK of 3 to see the state of the record before the change...
Yeap that is the basic concept. Although i think your users will be more interested in seeing the whole change history at once. That way they can see that columna was change to 'xyz' on such and such date and the columnb was changed on this date etc...
_______________________________________________________________
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/
September 6, 2011 at 1:15 pm
Having the full history isn't as important as being able to see what was just changed, but obviously I can give them the option of both which is at the heart of the entire project "the information you need, they way you need it."
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply