需要 SQL Server 中的 datetime 列，该列在修改记录时自动更新

2022-01-13 00:00:00 timestamp tsql sql-server

我需要在 SQL Server 中创建一个新的 DATETIME 列，该列将始终包含记录的创建日期，然后每当记录被修改时它需要自动更新.我听人说我需要一个触发器，这很好，但我不知道怎么写.有人可以帮助使用触发器的语法来完成此操作吗?

I need to create a new DATETIME column in SQL Server that will always contain the date of when the record was created, and then it needs to automatically update whenever the record is modified. I've heard people say I need a trigger, which is fine, but I don't know how to write it. Could somebody help with the syntax for a trigger to accomplish this?

在 MySQL 术语中，它应该和这个 MySQL 语句完全一样:

In MySQL terms, it should do exactly the same as this MySQL statement:

ADD `modstamp` timestamp NULL 
    DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

这里有一些要求:

  • 当行被修改时，我无法更改我的 UPDATE 语句来设置字段，因为我无法控制写入记录的应用程序逻辑.
  • 理想情况下，我不需要知道表中任何其他列的名称(例如主键)
  • 它应该简短而高效，因为它会经常发生.
  • I can't alter my UPDATE statements to set the field when the row is modified, because I don't control the application logic that writes to the records.
  • Ideally, I would not need to know the names of any other columns in the table (such as the primary key)
  • It should be short and efficient, because it will happen very often.

推荐答案

SQL Server 无法为 UPDATE 定义默认值.

SQL Server doesn't have a way to define a default value for UPDATE.

所以你需要添加一个带有默认值的列来插入:

So you need to add a column with default value for inserting:

ADD modstamp DATETIME2 NULL DEFAULT GETDATE()

并在该表上添加触发器:

And add a trigger on that table:

CREATE TRIGGER tgr_modstamp
ON **TABLENAME**
AFTER UPDATE AS
  UPDATE **TABLENAME**
  SET ModStamp = GETDATE()
  WHERE **ID** IN (SELECT DISTINCT **ID** FROM Inserted)

是的，您需要为每个触发器指定一个标识列.

And yes, you need to specify a identity column for each trigger.

注意:在您不知道应用程序代码的表上插入列时要小心.如果您的应用有没有列定义的 INSERT VALUES 命令，即使在新列上使用默认值，它也会引发错误.

CAUTION: take care when inserting columns on tables where you don't know the code of the application. If your app have INSERT VALUES command without column definition, it will raise errors even with default value on new columns.

相关文章