Get date of last change in table?

[Philipp Lenssen]

How can I find out the date/time of the last change to the data within
a specific MSSQL table using ASP? How can I find out the last change to
the structure? How can I find out the last access?

Are these things logged by default and what do I write to grab the
values? Do I need to turn on some logging and if so does it cause
performance problems?

Help very much appreciated!

--
Google Blogoscoped
[url]http://blog.outer-court.com[/url]

[Bob Barrows [MVP]]

Philipp Lenssen wrote:

> How can I find out the date/time of the last change to the data within
> a specific MSSQL table using ASP?

Add a datetime column to the table and create an UPDATE trigger to update it
with GetDate().

> How can I find out the last change
> to the structure?

There is no builtin way to do this. You will need to maintain this value
yourself somewhere as part of your source control system.

If you restrict schema changes to CREATE statements, then sysobjects
contains a crdate column which is supposed to contain the date the object
was created. But ALTER statements will not update that column.

> How can I find out the last access?

You mean access as in SELECT statements? You can't. Oh, I suppose you could
restrict access to the tables and require the use of stored procedures
which log every use, but what's the point? This would bog down the
performance of your application.

>
> Are these things logged by default

No, with the proviso that all transactions that involve data modification
are logged in the transaction log. Third-party programs such as Lumigent's
Log Explorer and Entegra expose the log but that really doesn't help you
with getting the information via ASP.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron [SQL Server MVP]]

SQL Server doesn't track this information, you will have to do so yourself
or with the help of a 3rd party product. Certainly, any operational
activities you add are going to cost in terms of performance. Only you can
test it out and decide whether the performance implications are non-existent
or not acceptable.

[url]http://www.aspfaq.com/2448[/url]
[url]http://www.aspfaq.com/2495[/url]
[url]http://www.aspfaq.com/2496[/url]
[url]http://www.aspfaq.com/2499[/url]

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"Philipp Lenssen" <info@outer-court.com> wrote in message
news:2ibl92Fl81huU1@uni-berlin.de...

> How can I find out the date/time of the last change to the data within
> a specific MSSQL table using ASP? How can I find out the last change to
> the structure? How can I find out the last access?
>
> Are these things logged by default and what do I write to grab the
> values? Do I need to turn on some logging and if so does it cause
> performance problems?
>
> Help very much appreciated!
>
> --
> Google Blogoscoped
> [url]http://blog.outer-court.com[/url]

[Bullschmidt]

You could add a LastChangedDate to each record or create a table that
stores the last changed date for each table. Either way you'll need to
update these with each data add/update/delete you make to the table
somehow.

Best regards,
J. Paul Schmidt, Freelance ASP Web Consultant
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Devdex [url]http://www.devdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!