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

我有一个看起来像是 SQL Server 中数据库触发器的基本场景，但我遇到了一个问题。

我有桌子Users（身份证、姓名、电话等）我有桌子用户历史记录（id、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

有什么建议么？

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

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（用于插入和更新）和Deleted（用于更新和删除）。这些伪表包含新插入的值（或 UPDATE 中更新的值），或已删除（对于 DELETE）或已更新的值（更新之前的旧值，对于 UPDATE 操作）。

您需要注意，即使您更新大量行，例如，触发器也会被调用一次。Inserted and 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 and Updated tables:

• 如果两个计数都大于零，则为UPDATE
• if the Inserted表有行，但是Deleted不，这是一个INSERT
• if the Inserted表没有行，但是Deleted确实，这是一个DELETE

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

IF UPDATE(fieldname) ......

但这有点乏味。

或者你可以使用COLUMNS_UPDATED() http://msdn.microsoft.com/en-us/library/ms186329.aspx函数 - 然而，这并没有给你一个很好的列名列表，而是一个 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

我还没有包含确定哪些字段已更新的部分 - 这是留给读者的练习:-)

这些帮助有用？