logo标签列表

如何在 SQL Server 表中添加“最后修改”和“创建”列？

sql-servert-sqltriggerssql-server-2012
56

我正在为一个SQL Server 2012数据库设计一个新的数据库模式。

每个表格应该增加两个额外的列，分别叫做modified和created，在行被插入或更新时会自动改变。

我不知道怎样才能最好地实现这一点。

我认为触发器是处理它的最佳方式。

我尝试查找关于触发器的示例...但我找到的教程都将数据插入到另一张表格等操作。

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

如果有人想在mysql-phpmyadmin中完成此操作，那么非常容易。在创建表或添加新表时，将“默认值”设置为“CURRENT_TIMESTAMP”，并将“属性”设置为“on update CURRENT_TIMESTAMP”。这两个都可以通过下拉菜单设置。 - Vikram Baliga
6个回答
77

created列很简单 - 它只是一个DATETIME2(3)列，并带有一个默认约束，在插入新行时设置：

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指定值时，它将被设置为当前日期和时间。

modified有点麻烦，因为你需要为AFTER UPDATE情况编写触发器并更新它 - 你不能声明地告诉SQL Server为你完成此操作....

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伪表，其中包含使用你的基表的主键更新的所有行。

你需要为每个想要添加modified列的表创建这个AFTER UPDATE触发器。

4
非常感谢 @marc_s！再次感谢您的帮助！我想展示一个小演示SQL脚本供人们复制和修改，但它太长了，无法在评论中显示。只有一个提示。我发现设置默认约束的简单方法是使用以下语句：CREATE TABLE [dbo].[Table1]( [created] [datetime2](3) NOT NULL CONSTRAINT [DF_Table1_created] DEFAULT (sysdatetime()), ...) 再加上触发器 CREATE TRIGGER updateModified 即可完成！还有一个提示。表中的触发器名称必须在模式内唯一。CREATE TRIGGER dbo.Table1_updateModified。干杯，Stefan。 - stevo
1
这对于自动化来说绝对是非常棒的。感谢你们两个。 - Alper
我认为像其他答案建议的那样添加SET NOCOUNT ON语句以提高性能是明智的。 - Jordi
15
通常，您可以拥有以下列：
  • LastModifiedBy
  • LastModifiedOn
  • CreatedBy
  • CreatedOn
其中LastModifiedBy和CreatedBy是对users表（UserID）的引用，而LastModifiedOn和CreatedOn列是日期和时间列。
您有以下选项：

  1. 无触发器解决方案 - 有人曾说过：“最好的编写触发器的方法是不编写触发器。” 并且你需要知道，通常它们会影响性能。因此，如果可以避免使用它们，最好这样做，即使在某些情况下，使用触发器可能看起来是最简单的事情。

    因此，只需编辑所有的INSERT和UPDATE语句，以包含当前的UserID和当前日期和时间。 如果无法定义此类用户ID（匿名用户），则可以改用0，而列的默认值（如果未指定user ID）将为NULL。 当插入NULL值时，您应该找到“问题所在”的语句并进行编辑。

  2. 触发器解决方案 - 您可以创建AFTER INSERT, UPDATE触发器，并在其中填充用户列。 在触发器上下文中获取当前日期和时间很容易（例如使用GETUTCDATE()）。 这里的问题是触发器不允许传递/接受参数。 因此，由于您没有插入user ID值，也无法将其传递给触发器。 如何找到当前用户？

    您可以使用 SET CONTEXT_INFO 和 CONTEXT_INFO。 在所有insert和update语句之前，必须使用SET CONTEXT_INFO将current user ID添加到当前上下文中，并在触发器中使用CONTEXT_INFO函数来提取它。

因此，当使用触发器时，您需要再次编辑所有的INSERT和UPDATE语句 - 这就是为什么我更喜欢不使用它们。

无论如何，如果您只需要日期和时间列，而不是创建/修改列，则使用触发器更加耐用和容易，因为您现在和将来都不需要编辑任何其他语句。

使用 SQL Server 2016，我们现在可以使用SESSION_CONTEXT函数读取会话详细信息。这些详细信息是使用sp_set_session_context设置的（可为只读或读写）。这些东西略微用户友好：

EXEC sp_set_session_context 'user_id', 4;  
SELECT SESSION_CONTEXT(N'user_id');

一个不错的示例。

非常感谢@gotqn的回答！我尝试通过源代码来管理它，因为实际上我还需要LastModifiedBy和CreatedBy。我正在使用Adam Schroder的NPoco与存储库模式，并且我很喜欢它！但是我在处理通过代码处理它的方法时有些困难。我是ORM世界的新手。但我会继续努力的 ;) - stevo
触发器出了问题吗？ - Kiquenet
3

注意，上述方法在某些情况下可能无法正常工作， 我浪费了很多时间，后来发现以下方法很有用：

create TRIGGER yourtable_update_insert
ON yourtable
AFTER UPDATE 
as
begin
   set nocount on;
   update yourtable set modified=getdate(), modifiedby = suser_sname()
   from  yourtable t 
   inner join inserted i on t.uniqueid=i.uniqueid 
end
go

set nocount on;是必需的，否则你会收到以下错误信息：

Microsoft SQL Server Management Studio

未更新任何行。

第5行数据未提交。 错误来源：Microsoft.SqlServer.Management.DataTools。 错误信息：更新或删除的行值要么不唯一，要么修改了多个行（2行）。

请纠正错误后重试，或按ESC取消更改。

确定 帮助

1
将您的答案格式化为代码块会大大提高可读性和理解性。 - hmiedema9
2
CREATE TRIGGER [dbo].[updateModified]
   ON  [dbo].[Transaction_details] 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.Transaction_details
    SET ModifedDate = GETDATE() FROM dbo.Transaction_details t JOIN inserted i ON 
    t.TransactionID = i.TransactionID--SYSDATETIME()
END
1
我使用了上面的代码来修改列，并且使用了这个来创建列...我修改了该列，因为我已经创建了它 alter table Table_Name add CONSTRAINT constraint_name DEFAULT (SYSDATETIME()) for Column_Name - G.Protocol
2
重要的一点是，你应该始终将所有表和行的插入/更新时间设置为相同的时间源。如果你不使用触发器，就存在一个危险，即对表进行直接更新的不同应用程序可能在具有不同时钟时间的计算机上运行，或者在应用程序层中没有一致地使用本地时间和UTC。
考虑这样一种情况，即进行插入或更新查询的系统直接设置了更新/修改时间值，并且其时钟比实际时间慢5分钟（虽然不太可能，但值得考虑），或者正在使用本地时间而非UTC时间。如果另一个系统使用1分钟的间隔轮询，则可能会错过更新。
出于多种原因，我从不直接向应用程序公开我的表。为了处理这种情况，我在表上创建一个视图，明确列出要访问的字段（包括更新/修改时间字段）。然后，在视图上使用INSTEAD OF UPDATE、INSERT触发器，使用数据库服务器的时钟明确设置updatedAt时间。这样，我可以保证数据库中所有记录的时间基准相同。
这有几个好处：
  1. 它只对基础表进行一次插入，你不必担心级联触发器被调用
  2. 它允许我在字段级别控制我向业务层或其他数据使用者公开的信息
  3. 它允许我单独保护视图，而不是基础表
在SQL Azure上运行得非常好。
请看一下视图上触发器的示例：
ALTER TRIGGER [MR3W].[tgUpdateBuilding] ON [MR3W].[vwMrWebBuilding]
INSTEAD OF UPDATE, INSERT AS
BEGIN

SET NOCOUNT ON

IF EXISTS(SELECT * FROM DELETED)
BEGIN
    UPDATE [dbo].[Building]
       SET 
          ,[BuildingName] = i.BuildingName
          ,[isActive] = i.isActive
          ,[updatedAt] = getdate()
     FROM dbo.Building b
     inner join inserted i on i.BuildingId = b.BuildingId
END
ELSE
BEGIN
INSERT INTO [dbo].[Building]
           (
            [BuildingName]
           ,[isActive]
           ,[updatedAt]
           )

           SELECT 
               [BuildingName]
              ,[isActive]
              ,getdate()
              FROM INSERTED
    END
END

我希望这可以帮到您，如果有更好的解决方案，欢迎留言评论。
我正在寻找使用这种方法的保证，因为我刚刚发现了INSTEAD OF触发器（我是Sql Server的新手）。我认为从性能上讲，它比AFTER触发器更好，因为它用包含数据的操作替换了传入的操作，而不是添加更新操作。但是，如果插入/更新很少且影响多行，例如在数据仓库中，并且没有太多单行操作，则差异可能很小。 不太好的一面是，似乎如果我们向表中添加列，则应更新触发器定义以添加该新列？ - Raúl Moreno
0

这个解决方案可能并不适用于所有的使用情况，但在可能的情况下，它是一种非常干净的方式。 为插入/更新表中的行创建一个存储过程，并且只使用此存储过程来修改表。在存储过程中，您可以随时根据需要设置创建和更新列。例如，设置updatedTime = GetUTCTime()。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接