Time Stamp 1

[woyler]

Hi,
What is the best approach to time stamping a field whenever that row is inserted or modified? I have a couple of tables with a field named MOD_TMSTMP. I want it to update with the current system date/time when changed. I had a trigger to do this, but as I run into loading record counts over 500 records, the process became very slow. I am running SQL 2k A would appreciate any direction. Thanks
Bill

 

[woyler]

I have found that putting the GetDate() function as a formula for that field works. Is there a better way?

 

[tlbroadbent]

I would set getdate as the default for inserts and create an update trigger to update the column to the current datetime by setting it to getdate. Make sure you don't fire the trigger for inserts, also. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[woyler]

That makes good sense. One last one. Is it possible to make one trigger named, say, update_time and have multiple tables use it rather then assigning the same code to every object that I need in the database?
Thanks again
Bill

 

[tlbroadbent]

Triggers are created per table. A trigger can update multiple tables but cannot be fired by updates to multiple tables. You could write a stored procedure and have triggers execute it but I'm not sure that would be any easier than just adding a simple update query in a trigger for each table. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[woyler]

Thanks for your time Terry.
Bill