Creating a Date Updated Field in Tables

By Chris Miller on 24 August 2000 | 5 Comments | Tags: Database Design


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"

There really isn't any other way other than using a trigger to do what you're trying to do. You may want to look at writing the shell for the script using a query, and then go from there. The query below works, but it assumes that the primary key for each table is called "id". It would be pretty easy to make it figure out how to do a single-column primary key and substitute that in, but composite keys would be a bit more difficult and would likely need a cursor, and I didn't have time to write one of those here. This should get you started, though:

select 'create trigger ' + name + '_updatedate on ' + name + ' for update not for replication
as
update ' + name + ' set update_date = getdate()
where id in (select id from inserted)
'
from sysobjects where type = 'u'


rocketscientist.

Discuss this article: 5 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Using SET NULL and SET DEFAULT with Foreign Key Constraints (12 August 2008)

Implementing Table Interfaces (19 May 2008)

Implementing Table Inheritance in SQL Server (20 February 2008)

The Daily Database Build (23 August 2004)

HOW TO: Move a Database Diagram (12 December 2003)

Database Design Thoughts for Packaged Applications (26 October 2003)

The current state of database research (16 September 2003)

Using Metadata (24 March 2003)

Other Recent Forum Posts

ORA-01795 - max. 1000 in a list (3 Replies)

Record count error? (3 Replies)

Insert Statement (9 Replies)

Query (5 Replies)

What's the equivalent "Multiphase Data Pump" SSIS (1 Reply)

Group by to get the remain qty (1 Reply)

LOGIN PROBLEMS (4 Replies)

Commit Frequency Vs DBCC SHRINKLOG FILE (12 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -