Re: How to inquire if a database has changes?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Thu, 15 Nov 2007 08:08:00 -0800, Vicente Flich
<VicenteFlich@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hello,

There are any way to inquire trough odbc if a table or an entire database
have been changed in SQL 2005? or how to know the time a table have been
changed?

I need this to optimize the select from my application. I have tables in the
database than change rarely, then I think I can do the SELECT * FROM table
when my program start and hold the resultset to use within the application.
Only when these tables are actualized (rarely) I need to re-select. Any idea
to do this?

Thank

Vicente Flich

There might be a system table or view which you could query for the
date of the latest change to the table(s) of interest. However, it
would not be portable; and since you are using ODBC, there is always
the possibility that the back end server might change some day.

The portable way of doing this would be to use a BEFORE UPDATE
trigger and store that information in some special table set aside to
keep auditing information such as this. Another possibility would be
to store the data directly in the table in some kind of date/time
column and compare it to the current system or local time -- for
example, on DB2 you could use the TIMESTAMP data type; however, I
believe this means something entirely different on MS-SQLServer.

--
Bob Hairgrove
NoSpamPlease@xxxxxxxx
.