创建用于插入，修改和删除的数据库触发器的正确​​语法是什么

Question

我在SQL Server中有一个数据库触发器的基本情况，但遇到了问题。

我有表 Users （标识，名称，电话等），并且我有表 UsersHistory （标识，user_id操作，字段，时间戳）

我想要一个数据库触发器，随时可以将其插入，更新或删除到用户中，我希望在UsersHistory中创建一个具有用户ID和已完成操作的新记录（插入新的，更新的字段，已删除的ID。基本上是审计日志表。

这是我走了多远，但我不知道如何：

1. 获取有关修改和删除的ID，以及
2. 如何获取已更改字段和已执行操作（插入，删除，更新）的列表

CREATE TRIGGER Update_Users_History 
   ON  Users
   AFTER INSERT,DELETE,UPDATE
 AS 
 BEGIN
-- Insert statements for trigger here

insert into UsersHistory (user_id, [action], [fields], timestamp)
select  max(id) as user_id, {action ??},{fields??}  getdate() from Users)

END
GO

有什么建议？

Answer 1

最简单的方法可能只是简单地创建三个触发器-每个操作一个：

CREATE TRIGGER trgUserInsert
ON dbo.User AFTER INSERT
AS BEGIN
   INSERT INTO dbo.UserHistory............
END

CREATE TRIGGER trgUserDelete
ON dbo.User AFTER DELETE
AS BEGIN
   INSERT INTO dbo.UserHistory............
END

CREATE TRIGGER trgUserUpdate
ON dbo.User AFTER UPDATE
AS BEGIN
   INSERT INTO dbo.UserHistory............
END

这样，事情就很简单，您可以轻松地了解自己在做什么，如果您需要插入或删除大量项目，它还可以关闭单个操作的触发器。

在触发器内部，您有两个“伪表”-
Inserted（用于INSERT和UPDATE）和Deleted（用于UPDATE和DELETE）。这些伪表包含新插入值（或UPDATE中更新的值）的值，或已删除（对于DELETE）或已更新的值（对于UPDATE操作，在更新之前的旧值）的值。

你需要知道，如果你更新行，如数量庞大的触发将有一次甚至被称为Inserted以及Deleted通常包含多个行。

作为示例，您可以像这样编写“ AFTER INSERT”触发器（只是猜测您的表结构可能是…。）：

CREATE TRIGGER trgUserInsert
ON dbo.User AFTER INSERT
AS BEGIN
   INSERT INTO 
      dbo.UserHistory(UserID, Action, DateTimeStamp, AuditMessage)
      SELECT 
         i.UserID, 'INSERT', getdate(), 'User inserted into table'
      FROM
         Inserted i
END

您正在寻找一种方法来找出此触发器引起的“动作”吗？我看不到有任何方法可以做到，这是将三个触发器分开的另一个原因。找出这一点的唯一方法是对Inserted和Updated表中的行进行计数：

• 如果两个计数都大于零，则为 UPDATE
• 如果Inserted表中有行，但表Deleted中没有，则为INSERT
• 如果Inserted表没有行，但是有Deleted，那是一个DELETE

您还正在寻找“已更新的字段列表”-再次，您实际上将没有任何简单的解决方案。您可以只浏览“用户”表中感兴趣的字段，然后检查

IF UPDATE(fieldname) ......

但这有点乏味。

或者，您可以使用该COLUMNS_UPDATED()函数-
但是，该函数不能为您提供很好的列名列表，而是一个VARBINARY，其中每个列基本上都是一位，并且如果打开了该列，则该列也将被更新。不是很容易使用.....

如果您确实要创建一个大型触发器，则可以以此为基础-它检测导致触发器触发的操作，并将条目插入到User_History表中：

CREATE TRIGGER trgUser_Universal
ON dbo.Users
AFTER INSERT, UPDATE, DELETE
AS BEGIN
  DECLARE @InsHasRows BIT = 0
  DECLARE @DelHasRows BIT = 0

  IF EXISTS(SELECT TOP 1 * FROM INSERTED)
    SET @InsHasRows = 1

  IF EXISTS(SELECT TOP 1 * FROM DELETED)
    SET @DelHasRows = 1

  DECLARE @TriggerAction VARCHAR(20)

  IF @InsHasRows = 1 AND @DelHasRows = 1 
     SET @TriggerAction = 'UPDATE'
  ELSE
     IF @InsHasRows = 1
        SET @TriggerAction = 'INSERT'
     ELSE  
        SET @TriggerAction = 'DELETE'

  IF @InsHasRows = 1
    INSERT INTO dbo.UsersHistory(user_id, [action], [fields], timestamp)
      SELECT i.UserId, @TriggerAction, null, getdate()
      FROM INSERTED i
  ELSE  
    INSERT INTO dbo.UsersHistory(user_id, [action], [fields], timestamp)
      SELECT d.UserId, @TriggerAction, null, getdate()
      FROM DELETED d
END

我还没有弄清楚哪些字段已经更新过了-留给读者练习:-)

这些帮助有用？