SQL Server 2005 自动更新日期时间列 - LastUpdated

发布于 2024-07-04 18:09:37 字数 828 浏览 6 评论 0原文

我定义了一个表（请参阅下面的代码片段）。如何添加约束或其他约束，以便在行更改时 LastUpdate 列自动更新？

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  
)

原文

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  
)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

随风而去 2024-07-11 18:09:37

为此，您必须使用触发器。

回复收藏 0 原文

jJeQQOZ5 2024-07-11 18:09:37

我同意其他人的观点——在 LastUpdate 列上设置 GetDate() 的默认值，然后使用触发器来处理任何更新。

只是像这样简单的事情：

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

如果您想要真正喜欢，让它评估正在更改的内容与数据库中的内容，并且仅在存在差异时才修改 LastUpdate。

考虑一下...

上午 7 点 - 创建的用户“jsmith”的姓氏为“Smithe”（哎呀），LastUpdate 默认为上午 7 点
上午 8 点 -“jsmith”向 IT 发送电子邮件说他的名字不正确。您立即执行更新，因此姓氏现在是“Smith”，并且（感谢触发器）LastUpdate 显示上午 8 点
下午 2 点 - 您的偷懒同事终于厌倦了 StumbleUpon，并检查了他的电子邮件。他看到了“jsmith”之前关于更名的消息。他运行：UPDATE Profiles SET LastName='Smith' WHERE Username='jsmith' 然后
回到 MySpace 冲浪。然而，触发器并不关心姓氏已经是“Smith”，因此 LastUpdate 现在显示下午 2 点。

如果您只是在更新语句运行时盲目地更改 LastUpdate，那么它在技术上是正确的，因为确实发生了更新，但实际比较更改并采取相应行动可能更有意义。这样，同事的下午 2 点更新语句仍会运行，但 LastUpdate 仍会显示上午 8 点。

——凯文

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

回复收藏 0 原文