DateTime SQL Server 2005 Automatic Updated Column - LastUpdated

Question

I have a specific table (see code snippet below). How to add a constraint or whatever so that the LastUpdate column is automatically updated when the row changes?

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

defaul restriction only works on inserts, use a trigger to update

Answer 2

I agree with others - set the default value for GetDate () in the LastUpdate column, and then use the trigger to handle any updates.

Just something 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 truly introduce yourself, evaluate what has changed compared to what is in the database, and only change LastUpdate if there is a difference.

Consider this ...

• 7am . User jsmith is created with the last name "Smithe" (oops), LastUpdate defaults to 7 am

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

• 2pm - Your current employee is finally bored with StumbleUpon and checks his email. He sees an earlier message from jsmith regarding a name change. It starts: UPDATE Profiles SET LastName = 'Smith' WHERE Username = 'jsmith', and then goes back to surfing on MySpace. The trigger doesn't care that the last name was already "Smith," so LastUpdate now shows 2pm.

If you simply blindly change LastUpdate whenever the update instruction is executed, it is TECHNICAL because the update has occurred, but it probably makes sense to actually compare the changes and act accordingly. So the Update 2pm Update statement will work, but LastUpdate will still show 8am.

- Kevin

Answer 3

I agree with the idea of ​​a trigger, although I would use a connection to insert instead of a subquery. However, I want to point out that the username is a particularly poor choice for the primary key. Usernames change frequently, and when you need to change all related tables. It is much better to have the user ID as the key, and then put a unique index in the username. Then, when the username changes, you do not need to change anything.

Answer 4

You will have to use triggers for this.

Answer 5

My suggestion was to create a stored procedure that uses lastUpdate for getdate () by default.

I tried to avoid triggers in the past because pre-SQL2005, where they were located and edited, was a pain in the grains. Especially for developers who are not familiar with your project.

Also add this as the default value for the column definition.