如何添加“最后修改的”和“创建的” 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.

每个表应具有两个额外的称为的列修改和创建的应该会在插入或更新行后自动更改。

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我不知道到达那里的最佳方法。

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.

推荐答案

创建的列很简单-只是一个 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())

因此，当您在中插入一行时表，并且未为已创建指定值，则将其设置为当前日期&

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.

修改后的 还要花点功夫，因为您需要为之后更新用例并进行更新-您不能以声明的方式告诉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

您需要加入插入的伪表，该伪表包含所有行，这些行已经用您的<

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.

您必须创建此更新后触发每个要在其中修改了 列的表。

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屋！