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 |
|
BatCat
Starting Member
3 Posts |
 Posted - 2011-06-16 : 10:48:39
|
| Hi Folks,Basically here is my problem.I have a shadow table that every time a change is made in another table get two rows written to it. One row containing the values before the change. One row containing the values after the change. There are 18 columns in the database table. Each of these could be altered.Does anyone have any idea how I can the values before the change and the values after the change to work out which specific column values were altered during the edit? I've worked with Oracle before but this is the first time I've really dealt with SQL Server like this. I imagine I should alias the table to get all the 'Before Update' entries and all the 'After Update' entries but I don't know how to do the comparison.Thanks,BatCat |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-06-16 : 11:09:43
|
how do you identified which 2 rows that are related ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
BatCat
Starting Member
3 Posts |
Posted - 2011-06-16 : 12:07:59
|
| Hi,Thanks for your reply!There is a unique id for the item being changed, and also a timestamp. This should be identical in both records. So I think I could relate the two records that way. I just don't know how to figure out the changed column values.Thanks,BatCat |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-06-16 : 18:31:05
|
quote:
There is a unique id for the item being changed, and also a timestamp
if you have more than one records being updated at the same time, all of these records might have the same timestamp.Also somehow you will need to identify which record is the BEFORE which is the AFTER.If these records is a result of the audit trigger, you should consider changing the trigger to provide such information
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
Compare 2 rows and find the different values?