Solved

How to identify particular table value is modified when?

Posted on 2014-03-06
8
218 Views
Last Modified: 2014-03-10
Hi Experts,

 I've table called tBalance. For Player id 1040 the value IsVoid was 1 intially. Someone has updated this values as 0. How to ensure the customer that it was preiously one and now it is zero?

  Kindly advice.
0
Comment
Question by:Easwaran Paramasivam
8 Comments
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 167 total points
ID: 39909017
Hello,

If you do not have an earlier backup of the database where the value was 1 and if you are not maintaining an audit table for the changes, then the only solution may be to read from the transaction log (if you have NOT TRUNCATED the log after the update). If the log was cleared after the update was made, I doubt there may not be any way out.

Here is a blog that details how to read from transaction log.
http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

-Harish
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 167 total points
ID: 39910889
My suggestion is to create a "audit" table if this is needed for any changed record and put a trigger on the parent OLTP table to populate the "Audit" table with all relevant info - who/when modified it, and what action - insert/update or delete?
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39911696
The audit could implement now. But the question with the existing modified table which did not implement it. Is there no other way to track it?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39911778
Did you try reading from the transaction log? If the update had happened prior to last database backup, you may have to read from the transaction log of from the backup.

-Harish
0
 
LVL 10

Expert Comment

by:Monica P
ID: 39911876
To track the updates what i have used is ..

This in case if update is made from website :

I have written the function in .net code to track the logged user and his action (insert,update,delete) into another table...Using that table i can view the Action performed by that user.

to Track all updates  in that table .,
you can also  use trigger to write  a log in another table.,  to track the person & operation he done.

I not know this solution is best...just shared  my view...
0
 
LVL 5

Assisted Solution

by:Pankaj_401
Pankaj_401 earned 166 total points
ID: 39912032
Not sure about how you will find earlier changes made in SQL server.
But if you have Change Data Capture enabled, you could have better chance to track all the changes made in SQL.
Also, check this MS-Library.
A good resource you can also explore here for further assistance.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39914056
Is there no other way to track it?
No.
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39917033
Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loops and updating in SQL Query 9 52
JSON  parse help 7 40
How to share My.Settings in multiple application instances ? 7 23
Groupbox Control ? 2 17
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question