如何添加“上次修改"和“创建"?SQL Server 表中的列? [英] How do I add a “last modified” and "created" column in a SQL Server table?

问题描述

我正在为 SQL Server 2012 数据库设计一个新的数据库架构.

I'm design a new db schema for a SQL Server 2012 database.

每个表都应该有两个额外的列，称为 modified 和 created，它们应该会在插入或更新行后自动更改.

Each table should get two extra columns called modified and created which should be automatically change as soon a row gets inserted or updated.

我不知道到达那里的最佳方式是什么.

I don't know how rather the best way to get there.

我认为触发器是最好的处理方式.

I assuming that trigger are the best way to handle it.

我试图找到带有触发器的示例..但是我发现的教程在另一个表中插入数据等.

I was trying to find examples with triggers.. but the tutorials which I found insert data in another table etc.

我认为这是一个很常见的场景，但我还没有找到答案.

I assumed it's a quite common scenario but I couldn't find the answer yet.

推荐答案

created 列很简单 - 只是一个带有默认约束的 DATETIME2(3) 列插入新行时设置:

The created column is simple - just a DATETIME2(3) column with a default constraint that gets set when a new row is inserted:

Created DATETIME2(3) 
   CONSTRAINT DF_YourTable_Created DEFAULT (SYSDATETIME())

因此，当您在 YourTable 中插入一行并且没有为 Created 指定值时，它将被设置为当前日期 &时间.

So when you insert a row into YourTable and don't specify a value for Created, it will be set to the current date & time.

modified 需要做更多的工作，因为您需要为 AFTER UPDATE 案例编写触发器并更新它 - 您不能声明性地告诉 SQL Server为你做这个....

The modified is a bit more work, since you'll need to write a trigger for the AFTER UPDATE case and update it - you cannot declaratively tell SQL Server to do this for you....

Modified DATETIME2(3)

然后

CREATE TRIGGER updateModified
ON dbo.YourTable
AFTER UPDATE 
AS
   UPDATE dbo.YourTable
   SET modified = SYSDATETIME()
   FROM Inserted i
   WHERE dbo.YourTable.PrimaryKey = i.PrimaryKey

您需要加入 Inserted 伪表，其中包含使用该表的主键上的基表更新的所有行.

You need to join the Inserted pseudo table which contains all rows that were updated with your base table on your primary key for that table.

并且您必须为要在其中添加 modified 列的每个表创建此 AFTER UPDATE 触发器.

And you'll have to create this AFTER UPDATE trigger for each table that you want to have a modified column in.

这篇关于如何添加“上次修改"和“创建"?SQL Server 表中的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！