SQL Server 2005 Auto Updated DateTime Column - LastUpdated

Question

I have a table defined (see code snippet below). How can I add a constraint or whatever so that the LastUpdate column is automatically updated anytime the row is changed?

CREATE TABLE dbo.Profiles
(
        UserName                                varchar(100)            NOT NULL,
        LastUpdate                              datetime                NOT NULL  CONSTRAINT DF_Profiles_LastUpdate DEFAULT (getdate()),
        FullName                                varchar(50)             NOT NULL,
        Birthdate                               smalldatetime           NULL,
        PageSize                                int                     NOT NULL CONSTRAINT DF_Profiles_PageSize DEFAULT ((10)),
        CONSTRAINT PK_Profiles PRIMARY KEY CLUSTERED (UserName ASC),
        CONSTRAINT FK_Profils_Users FOREIGN KEY (UserName) REFERENCES dbo.Users (UserName) ON UPDATE CASCADE ON DELETE CASCADE  
)

Answer 1

I agree with the others -- set a default value of GetDate() on the LastUpdate column and then use a trigger to handle any updates.

Just something simple like this:

CREATE TRIGGER KeepUpdated on Profiles
FOR UPDATE, INSERT AS 
UPDATE dbo.Profiles 
SET LastUpdate = GetDate()
WHERE Username IN (SELECT Username FROM inserted)

If you want to get really fancy, have it evaluate what's being changed versus what's in the database and only modify LastUpdate if there was a difference.

Consider this...

• 7am - User 'jsmith' is created with a last name of 'Smithe' (oops), LastUpdate defaults to 7am

• 8am - 'jsmith' emails IT to say his name is incorrect. You immediately perform the update, so the last name is now 'Smith' and (thanks to the trigger) LastUpdate shows 8am

• 2pm - Your slacker coworker finally gets bored with StumbleUpon and checks his email. He sees the earlier message from 'jsmith' regarding the name change. He runs: UPDATE Profiles SET LastName='Smith' WHERE Username='jsmith' and then goes back to surfing MySpace. The trigger doesn't care that the last name was already 'Smith', however, so LastUpdate now shows 2pm.

If you just blindly change LastUpdate whenever an update statement runs, it's TECHNICALLY correct because an update did happen, but it probably makes more sense to actually compare the changes and act accordingly. That way, the 2pm Update statement by the coworker would still run, but LastUpdate would still show 8am.

--Kevin