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

You're going to have to use triggers for that.

Answer 2

a defaul constraint only works on inserts, for an update use a trigger

Answer 3

My suggestion would be to create a stored procedure which defaults the lastUpdate to getdate().

I've tried to avoid triggers in the past because pre-SQL2005 locating and editing them was a pain in the rump. Especially for developers who are new to your project.

Also add that as the default value for your column definition.

Answer 4

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

Answer 5

Thanks mate that code snippet has really helped me :-) I never even knew triggers existed until I read this!

Answer 6

I agree with the trigger idea, although I would use a join to inserted instead of a subquery. However, I want to point out that username is a particularly poor choice for a primary key. Usernames often change and when they do you need to change all related tables. It is far better to have a user id as the key and then put a unique index on username. Then when the user name changes, you don't need to change anything else.