June 19, 2012 at 3:48 am
Hi there,
How to get the previous row value in the version 2005. I wanted this to acheive a audit trail report using a history table. Please help..
Thanks,
Ananda
June 19, 2012 at 4:07 am
Unless you have a trigger or something that's storing the old values somewhere, you can't. SQL doesn't automatically keep historical values around.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 4:11 am
I already have the data in history table. Its just that I need to present them in client required format.
To achieve this I need to get the previous row value when processing the current row value.
In my history table there will be a row inserted from actual table for each update and insert.
In the report I have to show for each column the previous and the current value which I can achieve only by knowing the previous row value. I am having difficulty in achieving that. Please let me know if you need any more information. Thanks for your help.
June 19, 2012 at 4:59 am
The table definitions would help, plus sample data and a better description of what you're doing including what the 'required format' looks like.
Without knowing what you have, what's the chance that any of us can give you any useful advice?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 5:06 am
Here is my history table
auditid | idUser | firstname | lastname | auditaction |audittime
1 | 1 | aaaa | bbbb | I| 1/2/2012
2 | 1 | aaaa | cccc |U | 1/3/2012
3 | 2 |gggg | eeee | I |3/4/2012
3 | 2 |gggg| eefg |U |4/4/2012
My report should be like this
idUser | variable | OrigValue | ChangedValue | Changedtime
1 | firstname | aaaa | | 1/2/2012
1 |lastname |bbbb| | 1/2/2012
1 |lastname |bbbb | cccc | 1/3/2012
Hope this helps.
June 19, 2012 at 5:19 am
Not really. I certainly can't easily use that to test code
Read this to see the best way to post this to get quick responses.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply