Simple update Trigger with system date

[andyL]

I am new to using triggers, so I wanted to make sure this made sense before I made this live.

I want a field on my table to be updated with the current date every time the record is updated.

I created the following trigger on the table...

CREATE TRIGGER testUpdate ON [dbo].[testTable] 
FOR UPDATE
AS 
UPDATE [dbo].[testTable] 
SET 
 [dbo].[testTable].[updateMe] = getdate()
FROM inserted i
WHERE [testTable].[pkTest] = i.[pkTest]

I am using SQL Server 2000 and have recursive triggers turned off. Do you see any major flaws with the way I am doing this?

Thanks,
AndyL

[vb_dba]

I don't see anything wrong with your trigger, but I do have a question about why you want to create a trigger just to update the date the record was last updated.

How are you updating records in this table? Via a Stored Procedure? If so, then I would suggest updating the updateMe field to GetDate() in your stored procedure rather than incur the overhead of a trigger to do this for you.

[andyL]

Thanks,

The table does not get updated by a stored procedure, but thank you for the suggestion. I am using the data from this table in another application and I need to track new records and updated records, so I added two fields DateCreate and DateUpdate. I set the default value for DateCreate to GetDate() and will be using this trigger to update the DateUpdate field. This table only has around 100 records which do not have much activity so I don't believe there will be much overhead.

AndyL