Creating auto LastUpdated and FirstCreated columns

[OnTheAnvil]

I currently have an application that allows data entry. It's been in use for over a year and now I'd like to add two columns to the table in the database (SQL Server 2000) that the data is stored in. The first column will be a date called "FirstCreated" and I want it to always store the date the row was originally created. The second column is also a date called "LastUpdated" this should always be the date that the row was last updated/changed on. I know how to do this is my code but I was wondering is there a way to add these columns and have SQL Server do the date updating automatically? This way I don't have to hunt down every place I change or create rows in my code and add this functionality in. I was thinking in the "Formula" section in the design table wizard maybe there was a way to do this.

 

Thanks,

OnTheAnvil

[HJB417]

use a trigger for the 'lastupdated' column on UPDATE statements. Make FirstCreated have a default value of NOW(). I don't know how to create triggers on ms sql server =/

[OnTheAnvil]

Thanks HJB417. Now for a performance question: Do triggers take any significant amount of processing power on a system that has a large number of updates or are they relatively trivial? Also, does anyone else know how to set up triggers? I can probably figure it out by trial and error but I'd rather not screw it up.

 

Thanks.

[PlausiblyDamp]

http://www.xtremedotnettalk.com/showthread.php?t=92281 gives a very basic idea of the syntax needed to create a trigger, if you want a more specific example just ask...

The performance impact of a trigger really depends on what the trigger does, simply updating a field shouldn't cause too much in terms of performance issues - however other aspects (indexing, validation) may add their own overheads.