Hi All,

Is there any way to check if a tables data has changed, without checking
the
number of records and there values?

I'm thinking perhaps some sort of server assigned serial number that
changes
when the data changes, or perhaps simpler a 'last updated' date for the
table over all?

Thanks.


----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 2948 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

no
but how useful would that be? seems like you'd still need to check the
data to see what actually changed to make it meaningful.

Simon Harris wrote:
Show quoteHide quote

SQL Server doesn't track this.  You could easily add your own
UPDATE/INSERT/DELETE trigger that updates some value somewhere...



Show quoteHide quote

Thanks Aaron, sounds like a plan, I'll give it go! :)

Trey - I am plan to use this to check if I need to build a category list
again, this is use-often/change-little data, which requires a lot of
manipulation to make usable, so I'm only going to rebuild the list when the
data has changed.

Simon.


Show quoteHide quote

If data auditing is something you plan to do a lot and you want to invest
the funds, there are 3rd party tools like Lumigent Log Explorer that can
analyze transaction logs to determine what / when and who happened to a
table's data.
http://www.sql-server-performance.com/log_explorer_spotlight.asp

One way of comparing the value of all rows and columns in one table with
those of another, without specifying all columns, is the use the
CHECKSUM_AGG( ALL ) function. Read up on this in BOL.

Going forward...

You may want to add a column of type timestamp. It an 8 byte type populated
with SQL Server generated values that change whenever a row is updated.
However, Despite it's name, the values do not translate into a standard
date/time format.

You can also, implement a trigger on the table that logs inserts / updates /
deletes to an audit table. For example:

CREATE trigger EmployeeAudit on Employee
AFTER INSERT, UPDATE, DELETE
AS
insert into EmployeeAudit select getdate(), 'D', * from deleted
insert into EmployeeAudit select getdate(), 'I', * from inserted


Show quoteHide quote

convert flat file to native file format for bcp
The command(s) completed successfully. - Can i turn this output off?
preparing monthly report
Parsing spaces
COUNT (DISTINCT (myfield)) PROBLEM