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.
| Author |
Topic |
|
AskSQLTeam
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. |
|
|
ShadowChaser
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. |
 |
|
|
mohdowais
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):http://www.nigelrivett.net/AuditTrailTrigger.htmlhttp://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.htmlOwaisWe make a living out of what we get, but we make a life out of what we give. |
|
|
|
|
|
|
|
|
Article: Creating a Date Updated Field in Tables