| Author |
Topic |
|
RobertKaucher
Posting Yak Master
169 Posts |
 Posted - 2011-02-11 : 11:51:38
|
| I have 2 tables, Device and Status (1-1). I would like to keep a history of the values set for each device's status. We simply created an update trigger that checks the ID value of the status to see if it was changed and then inserts the data into a DeviceStatusHistory table. The info we want to track is simple so this seemed like a good way to go about it. But I was wondering how other DBA/SQL Programmers might go about doing the same thing. Is there a better way?===http://www.ElementalSQL.com/ |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-11 : 13:52:29
|
| do you mean that every time when the status of a device is changed you push the old record into DeviceStatusHistory (via trigger by deleting from device and status tables and inserting it into the history) and then add/insert a new one? |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-02-11 : 15:29:17
|
| Depending on your SQL version there is also CDC (Change Data Capture) that is designed for change tracking. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-02-12 : 04:02:42
|
| i think for this simple scenraio capturing history using trigger is ok.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
RobertKaucher
Posting Yak Master
169 Posts |
Posted - 2011-02-14 : 09:00:09
|
quote:
Originally posted by MIK_2008
do you mean that every time when the status of a device is changed you push the old record into DeviceStatusHistory (via trigger by deleting from device and status tables and inserting it into the history) and then add/insert a new one?
The trigger is on the Device table. Anytime a record is updated if the status value changes it writes the old value to the StatusHisory table.quote:
Originally posted by Lamprey
Depending on your SQL version there is also CDC (Change Data Capture) that is designed for change tracking.
It's currently on a 2005 system. I had actually considered the use of CDC, but that kind of killed the idea. My desire was more to know how other people are doing this in their current systems or even in past systems just to expand my knowledge of how people are solving/have solved similar problems.===http://www.ElementalSQL.com/ |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-02-14 : 09:28:24
|
| Google for C2Auditing sql server.PBUH |
|
|
|
RobertKaucher
Posting Yak Master
169 Posts |
Posted - 2011-02-14 : 10:10:56
|
quote:
Originally posted by Sachin.Nand
Google for C2Auditing sql server.PBUH
Thanks for the suggestion. I was not aware of that. But for what we are doing that is like using a jackhammer to kill a fly. Again, I'm more curious about how people are actually tracking history - not auditing. Meaning I am wondering what people have done and why - not just specific technologies that allow you to track changes. Again, specifically historic data (although auditing is not irrelevant) and reasons about why you picked method X. I can use Google to find methodology. I want to know your opinions regarding the method more than just be given a list of ways to do the tracking.===http://www.ElementalSQL.com/ |
|
|
|
soaphope
Starting Member
13 Posts |
Posted - 2011-02-14 : 17:18:46
|
| I like to keep "backup" copies of records in certain tables in our system when records are changed or deleted. Here is how I do it:(1) All updates and deletes are made through user functions. e.g. fnUpdateEmployee(id,fname,lname,title) or fnDeleteEmployee(id)(2) All "backup capable" tables have two extra columns: a backupid and a timestamp(3) Prior to updating fields, the update function inserts a copy of the existing record and sets the backupid field to the id (primary key) of the record that is about to be changed(4) The delete function does not actually delete the record, it simply sets backupid = id(5) To find the current set of records, query the table where backupid is null(7) To get a list of "deleted" records, query the table where backupid = id(6) To get a list of backup records, query the table where backupid = id of the record you are interested inI like this approach because it's very simple, doesn't require me to keep a separate table, and lets me easily display a record along with a list of optional backups of the record in client applications. It would be simple enough to add a column to track who changed the record if you need that (ie who created the backup record). If you'd like more detail let me know.Salah BoukadoumFounder, Soap Hope Where 100% of profits go to lift women from poverty - http://soaphope.com |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2011-02-14 : 22:33:52
|
| Depending on how long the historical data must live and how many changes there may be, I'll either use an "archive trigger" like you did or I'll use Type 2 Slowly Changing Dimensions where the historical data is maintained in the same table or a hybrid of the two.And, yes... I always use triggers for such things so I can catch even ad hoc manual changes. You just have to make sure the triggers are well written, high performance, set based code that's also not prone to much in the line of blocking.--Jeff Moden |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-02-15 : 00:34:03
|
| As of for me we had first implementted CDC when we upgraded to SQL Server 2008.But then found that any changes on table schema on which CDC is enabled caused the audit data to be deleted.Hopefully Microsoft fixes this in the next edition.Also one drawback with CDC was you could not track the changes made on user basis by default.The solution we came up was we modified the table where the audit is saved by default and added a column named UserName with default value SUSER_SNAME().In one of other projects we had to facilitate the auditing of the data at the application level(DAL level to be precise) where the queries were logged into a series of text files which would record them on a daily basis before being fired on to the Sql Server.The client was to concerned about the tlog file being filled up because of the server side auditing.PBUH |
|
|
|
RobertKaucher
Posting Yak Master
169 Posts |
Posted - 2011-02-15 : 12:11:45
|
| Thanks, everyone. You have given me some good stuff to consider.===http://www.ElementalSQL.com/ |
|
|
|
|
History Tables, How do you...?