How to tell if table updated?

Question

How to tell if table updated?

cedar72

Say Hey Kid

Points: 711
More actions
April 26, 2004 at 11:11 am

#107971

We have a process that populates a table from a flat file. Is there anyway I can tell when this table is updates? Is there a system field or anything of the like?

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

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Why not place a step at the end of the job to send out notification that the update is complete? Or assign a trigger to the table to log information to another table that updates have taken place?

Shamless self promotion - read my blog http://sirsql.net

cedar72 Say Hey Kid Points: 711 More actions · Answer 2

May just have to do that, I wasn't sure if there maybe was a system field to say when the table was last updated

M.van Leewen Mr or Mrs. 500 Points: 520 More actions · Answer 3

I have the same question. To solve my problem I have defined a date/time field in the tabel with the formula 'getdate()'. Everytime a record is inserted it will carry the date and time of update. In that way I can tell which updaterun effected the table.

I anyone has a more elegant of efficient way I also would like to learn that.

Menno

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 4

Menno,

Your solution will only work when you insert nwe records.

To be able to it with updates you have to implement a (befor) trigger on update if you want to upgrade the same record, or an after trigger if you only want to store the last update time in a separated table.

Using a separated table has the advantage to not to modify the original table structure (sometimes you are not allowed to do this).

As an asnwer to cedar72: No there is no system columns saying when a table has been updated you only can see when the table has been created (crdate column in the sysobjects table)

Bye
Gabor

Amit Jethva SSCarpal Tunnel Points: 4707 More actions · Answer 5

if you are on SQL 2000, use CHECKSUM_AGG function.

see BOL for sample on how to use this function.

-- Amit

"There is no 'patch' for stupidity."

Download the Updated SQL Server 2005 Books Online.

Chuck Ritenour SSC-Addicted Points: 420 More actions · Answer 6

Any particular reason you can't add the GetDate() to the insert statement?

I realize you are inserting from a flat file, but even if it's a DTS package using data transformation it's relatively simple to convert that to a SQL insert/select statement and then add the GetDate as the last column.

If you need to track updates as well as inserts, then you'll have to to go with the trigger.

Thanks, and don't forget to Chuckle