SQL Server 自动更新日期时间戳字段 [英] SQL Server automatic update datetimestamp field

问题描述

在 SQL Server 2008 R2 中"我试图在 SQL Server 中插入一个公式，该公式会将 LastUpdatedTimestamp 字段中的当前值更新为现在，即 getdate() 每记录更新的时间.

In SQL Server 2008 R2" I am trying to insert a formula in SQL Server that will update the current value in the LastUpdatedTimestamp field to now i.e. getdate() every time the record is updated.

推荐答案

您可以在 DateTime 字段上设置默认约束，这将导致在您插入新的时插入当前日期/时间行.

You can have a default constraint on your DateTime field that will cause the current date/time to be inserted when you insert a new row.

从那时起，您需要使用 AFTER UPDATE 触发器，该触发器将在每次更新行时更新您的日期/时间列.

From there on, you need to work with a AFTER UPDATE trigger that will update your date/time column each time the row is updated.

您不能像您说的那样使用公式"来执行第二个任务(在更新行时更新日期/时间戳) - 它在 SQL Server 中不能那样工作.

You cannot do this second task (updating a date/time stamp when updating the row) using a "formula" as you said - it just doesn't work that way in SQL Server.

您需要按照以下方式提供触发器:

You need to provide a trigger something along those lines:

CREATE TRIGGER trgYourTableUpdateTimestamp
  ON dbo.YourTable FOR UPDATE
AS BEGIN
   UPDATE 
      dbo.YourTable 
   SET 
      YourTimeStampColumn = GETDATE()
   FROM 
      Inserted Ins
   WHERE
      dbo.YourTable.SomeUniqueId = Ins.SomeUniqueId
END

这篇关于SQL Server 自动更新日期时间戳字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！