May 21, 2013 at 7:45 am
I'm curious if there is a built in way or a best practice that allows for recording changes in tables without having to create a history table for every table involved.
I've seen several systems where there is one table that has columns for tablename, columnname, oldvalue, newvalue, transactiondate that are populated before update using a trigger. This way seems the way to go. Wondering if this is a handcoded thing for each system, or a common best practice that I could look at.
May 21, 2013 at 8:07 am
Matthew Cushing (5/21/2013)
I'm curious if there is a built in way or a best practice that allows for recording changes in tables without having to create a history table for every table involved.I've seen several systems where there is one table that has columns for tablename, columnname, oldvalue, newvalue, transactiondate that are populated before update using a trigger. This way seems the way to go. Wondering if this is a handcoded thing for each system, or a common best practice that I could look at.
That is an option. The downside of this approach is that you have now have what is called an EAV pattern (entity attribut value). Consider how incredibly difficult it would be to know what an entire row looked like at a given point in time.
If you only want to capture the values and not "who dunnit" you might take a look at CDC (Change Data Capture).
_______________________________________________________________
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/
May 21, 2013 at 8:46 am
Thanks for replying. CDC was our first thought, but it doesn't have the data that was changed. This is a system that keeps track of who has access to what within our organization and I'd like to track things like when access is removed, when a user is terminated, etc.
The plan is to never really delete anything, just set an active/inactive flag so we always keep everything for historical purposes. We just want to track the when and what and by whom and I thought it might be easier to do this way. I'm just unsure of the how, and don't want to invest a lot of time into it in case there's an easy way to do it.
May 21, 2013 at 8:58 am
Matthew Cushing (5/21/2013)
Thanks for replying. CDC was our first thought, but it doesn't have the data that was changed. This is a system that keeps track of who has access to what within our organization and I'd like to track things like when access is removed, when a user is terminated, etc.The plan is to never really delete anything, just set an active/inactive flag so we always keep everything for historical purposes. We just want to track the when and what and by whom and I thought it might be easier to do this way. I'm just unsure of the how, and don't want to invest a lot of time into it in case there's an easy way to do it.
UGH. Soft deletes are a PITA to deal with. Especially on large tables.
Given what you are wanting to track I would think you would also want to track who made the change. CDC does not track that. That means you pretty much have to roll your own. Once you get a pattern going it really is not as bad as it sounds initially. You can script the current table, change the name and add a couple columns.
CDC was our first thought, but it doesn't have the data that was changed.
If you track only the "old" value you have the complete history. The value it was changed to is either in the next audit row or in the actual table. There really is no need to track values from both inserted and deleted. Just track the values from deleted. If you track both you have doubled the storage space of your audit and gained nothing.
_______________________________________________________________
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/
May 21, 2013 at 9:05 am
Sean Lange (5/21/2013)
If you track only the "old" value you have the complete history. The value it was changed to is either in the next audit row or in the actual table. There really is no need to track values from both inserted and deleted. Just track the values from deleted. If you track both you have doubled the storage space of ouraudit and gained nothing.
The more I thought about it, and talked with the guy doing the front end, the less I want to do soft deletes, so that's not really on the table. I don't have to worry about the inserted values, I'm more just interested in capturing what changed, and who made the change as far as updates and deletes.
So are you recommending that we do have historical tables to mirror regular tables and just throw the old row into the history table with a whoChanged and whenChanged extra columns and then update/delete from original table? The more I think about it, the more feasible that sounds.
May 21, 2013 at 9:23 am
Matthew Cushing (5/21/2013)
Sean Lange (5/21/2013)
If you track only the "old" value you have the complete history. The value it was changed to is either in the next audit row or in the actual table. There really is no need to track values from both inserted and deleted. Just track the values from deleted. If you track both you have doubled the storage space of ouraudit and gained nothing.
The more I thought about it, and talked with the guy doing the front end, the less I want to do soft deletes, so that's not really on the table. I don't have to worry about the inserted values, I'm more just interested in capturing what changed, and who made the change as far as updates and deletes.
So are you recommending that we do have historical tables to mirror regular tables and just throw the old row into the history table with a whoChanged and whenChanged extra columns and then update/delete from original table? The more I think about it, the more feasible that sounds.
Unless you have a mountain of tables and/or expect lots of changes this is about the simplest way to do to it. I would actually have a whoChanged and whenChanged on the main table. Then you just audit that right along with the rest of it.
Now comes the question of what causes the audit record to be inserted? Do you do this with a trigger or inside the CRUD proc?
You might even decide that audit rows are kept or deleted when the row in the main table is deleted. If you keep the history you can still have history for rows that are no longer in the main table.
For the trigger approach you would need to add both an update and a delete trigger. You can make use of the UPDATE function here too so you only track this if certain columns change (if you want). For the sproc approach you would need to have auditing logic in both sprocs.
Just keep in mind that this has the potential to slow down the system if you are not careful in how you implement this.
_______________________________________________________________
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/
May 21, 2013 at 9:30 am
I think the easiest way to do it would be using triggers on the table itself for update and delete. We already do have a createID, createDate, updateID, updateDate on the tables, so we're already to go on that. That'll only save the last update thought, and we want to have the info saved.
As for deleted records, we'd leave the history table alone when that happens to preserve the data in case we need to go back and look at it. When a user is removed from a system, it's just a case of unchecking an active box in the app, which updates the row, setting them inactive, and recording an endDate to their access so we can track when they were disabled.
As for speed, the system isn't high impact, nor large volume, so I think we're cool.
I guess my only question is to how we get the information of the user who is disabling the person, but since they're logged into the system, it should be pretty simple. I'm shaky on front end, so I'll talk to my developer about passing that to me so I can use it in the table update.
Thanks Sean!
Sean Lange (5/21/2013)
Unless you have a mountain of tables and/or expect lots of changes this is about the simplest way to do to it. I would actually have a whoChanged and whenChanged on the main table. Then you just audit that right along with the rest of it.Now comes the question of what causes the audit record to be inserted? Do you do this with a trigger or inside the CRUD proc?
You might even decide that audit rows are kept or deleted when the row in the main table is deleted. If you keep the history you can still have history for rows that are no longer in the main table.
For the trigger approach you would need to add both an update and a delete trigger. You can make use of the UPDATE function here too so you only track this if certain columns change (if you want). For the sproc approach you would need to have auditing logic in both sprocs.
Just keep in mind that this has the potential to slow down the system if you are not careful in how you implement this.
May 21, 2013 at 9:51 am
Matthew Cushing (5/21/2013)
I guess my only question is to how we get the information of the user who is disabling the person, but since they're logged into the system, it should be pretty simple. I'm shaky on front end, so I'll talk to my developer about passing that to me so I can use it in the table update.
Not sure if you need to add this as a new column. You already have this data on the row so the person "who dunnit" would be the value in that column on the "next" row. Certainly not a big deal to add it, especially since you already have the data in inserted.
Happy to help. Hope I was able to steer to a solution you are happy with.
_______________________________________________________________
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/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply