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 |
|
daquoc
Starting Member
35 Posts |
 Posted - 2006-03-03 : 02:39:49
|
| Help me making a trigger that tracks the data changes before and after a certain colunm is updated.Thankdaquoc |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-03 : 03:14:36
|
| 1. Where do you want to keep track of the data changed ? You can consider create another table to keep track of the changes2. make use of inserted and deleted table inside the trigger----------------------------------'KH' |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-03-03 : 03:37:38
|
| The data AFTER a column update is surely what is in the table itself. steve-----------Oh, so they have internet on computers now! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-04 : 00:57:24
|
| Yup, I'm with you on that elwoos.Store the BEFORE data in an audit table with identical columns to the main table, but additionally columns for Date, User and whether the action was a Delete or Update (i.e. do not audit the initial Insert 'coz that's in the main table itself).Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-03-06 : 04:53:09
|
Good grief Kristen we agree on something again!  This is getting to be a habit, people will start talking.steve-----------Oh, so they have internet on computers now! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-06 : 05:08:12
|
quote:
Originally posted by Kristen
Yup, I'm with you on that elwoos.Store the BEFORE data in an audit table with identical columns to the main table, but additionally columns for Date, User and whether the action was a Delete or Update (i.e. do not audit the initial Insert 'coz that's in the main table itself).Kristen
I agreed with you on the storing Date, User & Action as these provide answers to WHEN, WHO & WHAT on the audit point of view.On the point "do not audit the initial Insert 'coz that's in the main table itself", yes.So we have WHEN, WHO & WHAT was updated & deleted. But the information on WHEN & WHO was inserted is not audited !I actually have a requirement from the company's internal auditor that wants everything (not really every table but the transactional table) to be audited. So we implemented this on insert, update & delete trigger.----------------------------------'KH' |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-06 : 10:37:58
|
| "But the information on WHEN & WHO was inserted is not audited"Ah! ... We store Create User and Date, and Update User & Date, on ALL tables - so by the time it gets to our Audit table (i.e. on UPDATE or DELETE) that data is already present!Kristen |
|
|
|
daquoc
Starting Member
35 Posts |
Posted - 2006-03-06 : 19:43:50
|
| Yeah, I got itThank you.daquoc |
|
|
|
|
|
|
|
|
Tracking data changes