How to know the row got updated in SQL

Question

Post reply

How to know the row got updated in SQL

Arindam Basu

Valued Member

Points: 58
More actions
March 20, 2007 at 6:31 am

#116260

I want to know how do we come to know the date and time at which a particular row in a table got updated.
Say I have a table having some account details but there is no field which has the date and timestamp but I want to segregate the and pull only those records which got updated in a particular day.

Arindam Basu
Software Engineer
Hewlett Packard India Pvt Ltd.
Bangalore-17

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

You need a trigger to record the time and then a field or table to store the data in.

http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp

Arindam Basu Valued Member Points: 58 More actions · Answer 2

Hey Jones, Thanks but I need to know the result after updation. Say I have some 1000 records in table and the next day there are some 100 records being added and 50 records out of the existing 1000 got updated. Pls let me know a query which can fetch me 150 records.

Arindam Basu
Software Engineer
Hewlett Packard India Pvt Ltd.
Bangalore-17

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 3

Unfortunately, there is no way to know when a field is updated unless you have set up a table to log such events with a datetimestamp. Triggers can do this for you by having them fire on insert or on update. If you don't like triggers, you can do the same by writing some code within the stored proc to do monitor the inserts/updates. The key is the field with the datetimestamp. let me know and I can send you an example of something I wrote to track changes.

Marvin

Marvin Dillard
Senior Consultant
Claraview Inc

Arindam Basu Valued Member Points: 58 More actions · Answer 4

Marvin, The problem is I cannot alter the table structure of the client database and my application requires the data from the table which has got updated for the particular date

Arindam Basu
Software Engineer
Hewlett Packard India Pvt Ltd.
Bangalore-17

Sean May-415583 SSC Enthusiast Points: 144 More actions · Answer 5

If you can't alter the table, use the trigger to populate a different table containing your source table's key and a datetime field.

Fortunately (or unfortunately), if the trigger is not doable, you do have options, though they are less savory. One would be copying the data in your client table into a separate table once a day and running a horrible join to find any records with the same keys but different anything else. You wouldn't know exactly what time the changes took place, but you could make a safe assumption that they happened between the last time you refreshed your comparison table and "now". The only other thing I can think of at the moment would be using a tool to capture your transaction logs in a legible state (like Log Explorer) and parsing through the results every day, but that won't be fun to implement...

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 6

I understand your total issue now. There is no simple way to accomplish this, unless you compare the tables on a daily basis as Sean suggested. You might check with the client to see if they would like you to track changes for them, just in case it is something they have been thinking of.

Marvin Dillard
Senior Consultant
Claraview Inc