SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Creating a Date Updated Field in Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/08/2000 :  07:09:57  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 11/10/2000 :  11:41:11  Show Profile  Reply with Quote
Timestamp

Isn't it there's a special data type called "timestamp" ? Maybe you could create a field called DateUpdated and make it a "timestamp" data type?

This is a special value that is unique within a given database. The value is set by the database itself automatically every time the record is either inserted or updated - even if the timestamp column wasn't referred to by the UPDATE statement. - From SQL Server 7.0 Programming.

Ruel
ruel@web10hosting.com

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/29/2001 :  20:03:55  Show Profile  Reply with Quote

you can just make a datetime field in the tables, and use a default value of getdate() (You do this in the design window or in your t-sql code).

when you insert records into it you don't need to specify the field (and shouldn't) and the datetimes will be put there automatically. if there is existing data in the tables then this will leave them null.

the problem with this approach is that the datetime values are not guaranteed to be unique, where a timestamp is guaranteed to be unique database wide.

it's really up to you.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/29/2001 :  20:18:00  Show Profile  Reply with Quote

This could be potentially dangerous so i left out the call that will actually alter the tables, but you can see the output via Query Analyzer and run it without harming anything.

This would generate a datetime field in all of your tables and assign a default value of getdate.

Just use your noggin here, definetly don't cut and paste this and run it until you understand what it would do but hopefully this gives you some ideas.

You can easily generate the code needed to write all the update triggers as well.

There is prolly a MUCH better way of accomplishing this fyi!

--

set nocount on
use pubs
declare @table varchar(200)
declare @dynamicsql nvarchar(4000)
declare testcur cursor for

select name from sysobjects where xtype = 'u'

open testcur

fetch next from testcur into @table

while @@fetch_status =0
begin
set @dynamicsql = 'alter table [' + @table + '] ADD DATEFIELD datetime null DEFAULT Getdate()'
print @dynamicsql
-- exec(@dynamicsql) -- if you uncomment this line, your database will be changed!!!
fetch next from testcur into @table
end

close testcur
deallocate testcur

Go to Top of Page

ShadowChaser
Starting Member

1 Posts

Posted - 12/24/2003 :  15:00:37  Show Profile  Reply with Quote
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

mohdowais
Sheikh of Yak Knowledge

United Arab Emirates
1456 Posts

Posted - 12/25/2003 :  00:01:40  Show Profile  Visit mohdowais's Homepage  Reply with Quote
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):
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.html



Owais

We make a living out of what we get, but we make a life out of what we give.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000