Table last modified date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Table last modified date

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-17 : 06:43:24

Roger writes "Hi,

Is there a way to find out the date and time when a table was last modified by an insert, update or delete statement? Maybe this can be done by searching the system tables? Good design practice would suggest stamping each record with dateCreated and dateModified columns, but on tables where this was not done I would like to know if the information can be obtained from within the SQL database itself?

Thanks for you help on this.
Roger"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-17 : 07:16:32

no you can't.
add the extra columns as soon as possible.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-12-18 : 02:50:14

Can you re-tro fit this to the tables by adding the two columns ad using a DEFAULT for the dateCreated and an UPDATE TRIGGER to set the dateModified?

We have these two columns in all [almost without exception] our tables, plus we have the CreateUser and UpdateUser, and an EditNumber which is incremented each time the record is modified.

We also ahve "archive" tables for many of our tables - duplicating the structure of the main table, with no/few constraints, and using a DELETE TRIGGER to append data as it changes/is deleted.

Kristen

hueby
Posting Yak Master

127 Posts

Posted - 2005-01-06 : 17:50:09

Can you go into detail on how to use the "UPDATE TRIGGER to set the dateModified" ?? Is this a formula used, or default value of some sort? Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-07 : 01:28:19

create trigger x on tbl for update
as
update tbl
set datemodified = getdate()
from tbl t
join inserted i
on t.pk i.pk
go

Have a look at
http://www.mindsdoor.net/#Triggers

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2005-01-07 : 01:52:49

on t.pk = i.pk

Kristen

imughal
Posting Yak Master

192 Posts

Posted - 2005-01-07 : 01:58:47

hueby
Posting Yak Master

127 Posts

Posted - 2005-01-07 : 09:46:17

Okay, I've gotten this far with the trigger code:

create trigger lastmod on Equip_Used for update
as
update Equip_Used
set LastMod4 = getdate()
from Equip_Used
go

Edit: Fixed it. thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources