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

 All Forums
 Site Related Forums
 Article Discussion
 Article: Creating a Date Updated Field in Tables

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2000-08-08 : 07:09:57
John writes "I need to create a date updated field in all tables in my database. I want these to be automatically filled in by the database, on insert and update. I know I can use triggers to do this, but it means writing a trigger for each of the 80+ tables. Is there any better way of doing this? I am working on SQL server 7.0/win nt 4.0/ASP...Thanks, JohnA"

Article Link.

Starting Member

1 Post

Posted - 2003-12-24 : 15:00:37
There are a few *serious* flaws with this code and some of the responses made.

First off, the article. What's going to happen when you issue an "update" command on the same table in an update trigger? The update trigger will fire endlessly - you have programmed a recursive trigger. All updates to the table will fail as a result.

Someone made a response here to use the "timestamp" field. This, again, is *definately* not a good thing. The timestamp field in sql server does not work the same as the SQL standard - in SQL Server it is used for row versioning. It explicitly states in the help that it DOES NOT contain the date the row was updated!

Someone else said all you need to do is put a default value of "getdate()" into the row. That effectively creates a column tracking the date a row was CREATED, not when it was updated.
Go to Top of Page

Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-25 : 00:01:40
Good points, Shadowchaser. I hope Yukon does away with that misnomer "timestamp". Personally, I prefer the audit trail approach, not only is it more comprehensive, it can also be used for reporting purposes. You know how management is...

Here are links to a couple of wonderful articles by the wonderful Nigel Rivett (gosh, these links seem to have been passed around here really often recently):


We make a living out of what we get, but we make a life out of what we give.
Go to Top of Page

- Advertisement -