How to know new records

Question

How to know new records

LMtz

Mr or Mrs. 500

Points: 514
More actions
December 19, 2006 at 11:48 am

#90866

IS there a way to track or to identify last records inserted or updated into a database tables,

Thanks for any advice.

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

There is no way out of the box, you could easily develop a solution.

One way would to put a Created_Date field in your table with a default value of Getdate(). So when a record is created it will record the date and time.

mrpolecat SSCertifiable Points: 6964 More actions · Answer 2

you would also need to create a modify_date field with an update trigger that sets it to getdate() on record update to track the updates

SQLBill SSC Guru Points: 51440 More actions · Answer 3

For updating, create a trigger that when an update occurs, it also updates the created_date column (or whatever name you choose).

I suggest that instead of calling it Created_Date, you use something like Version_Date.

-SQLBill

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 4

Don't forget there is a table called "inserted" that contains newly added/updated records which you can access via a FOR INSERT or FOR UPDATE trigger.

mstjean Hall of Fame Points: 3310 More actions · Answer 5

If all access is via SPs, you could just be sure to refresh "UpdatedDate" field whenever you do an update, rather than using a TRG.

Upside is that it's a bit faster since you won't do a "double hit"; downside is that you cannot be 100% positive every update will happen via an SP, so someone *could* update the row without it getting reset... Whereas a TRG will positively set it.

If going the TRG route... you could also track "UpdatedBy"... and/or could also write this info to an audit table...

Cursors are useful if you don't know SQL