Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnneo
Starting Member
1 Post |
 Posted - 2007-07-26 : 11:29:18
|
| Auditing.I've searched through most of these forum looking for some information on how to do this properly, and i must admit that i'm rather lost. It is common practice to use triggers, but for this paticular scenario, I am required to write a stored procedure. This is for a table called Order_Item, and i am only interested in auditing 3 fields or so to an auditing table. Let's say that these fields are represented by item_text, item_font, and item_color. Would anyone mind helping me generate some code for this? Or list some specific ways in which i can do this? I have looked at using a temporary table for the old items, and then once the new values are written, comparing the record's id to all of those in the temporary table and then write that paticular old value to the audit table. I'm not entirely sure how to code that however, i'm still a novice with SQL. Any help would be greatly appreciated!Thanks in advance for any responses. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-26 : 11:50:56
|
| Before you do the update get the existing values and compare with the incoming values.Declare @curItemText .. , @CurItemFont ..., @CurItemColor ...SELECT @curItemText =Item_Text, @CurItemFont =Item_Font, @CurItemColor= Item_colorFROM Order_Itemwhere ITemId = @ItemIdThen compare against the incoming values, and if any of them are not same, insert into @curXXX the audit table. Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-27 : 02:04:05
|
| I take the approach of storing the "old" value whenever a new value iss created (rather than storing both the Old & New values of each change, and I don't store the Current value in the Audit table at all - that's in the database after all!)So if you are updating the Order_Item table for, say, OrderID=1234 and OrderItem=6789 then before doing the UPDATE I would "copy" the existing values for that row (or rows) to the Audit table.INSERT INTO AuditTable(AuditDate, Col1, Col2, Col3)SELECT GetDate(), Col1, Col2, Col3FROM Order_ItemWHERE OrderID=1234 AND OrderItem=6789I would do this regardless of whether they are about to change or not (the presence of an audit record where the values do NOT change tells you that the record was modified at that time, but that the audited fields were not changed, which might be useful in an investigation - obviously if this is not relevant to you then only Copy to the Audit table where the audited columns and about to change on the row(s) about to be updated)The problem with doing this in an SProc is that it is only going to audit changes which are made via the Sproc. So an UPDATE made directly on the table (e.g. by the DBA to fix some data issue, or by some program that is unaware of the SProc and the need for auditing) will bypass the Audit. Hence Triggers are more commonly used for this job."I've searched through most of these forum"Just in case you missed it here the FAQ link:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Triggers+to+audit+changesKristen |
|
|
|
|
|
|
|
|
Using a Stored Procedure instead of triggers for..