时间戳

首页 > 解决方案 > 用于填充审计表的基本 T-SQL 触发器

tsql - 用于填充审计表的基本 T-SQL 触发器

问题描述

阅读此页面后，我构建了几个表和一个触发器。这个想法是，当对第一个表执行or时INSERT，所操作的数据将插入到第二个审计表中。UPDATEDELETEMattMattAudit

触发器一定失败了，我不知道为什么；证据是审计表中没有任何条目，尽管CREATE TRIGGER后续ALTER TRIGGER语句成功完成。

主表Matt：

CREATE TABLE [dbo].[Matt](
    [MattID] [int] IDENTITY(1,1) NOT NULL,
    [Text] [nchar](10) NULL,
 CONSTRAINT [PK_Matt] PRIMARY KEY CLUSTERED 
(
    [MattID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

审核表MattAudit：

CREATE TABLE [dbo].[MattAudit](
    [MattAuditID] [int] IDENTITY(1,1) NOT NULL,
    [MattID] [int] NOT NULL,
    [Text] [nchar](10) NULL,
    [Action] [int] NOT NULL,
    [InsertedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_MattAudit] PRIMARY KEY CLUSTERED 
(
    [MattAuditID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

触发马特：

ALTER TRIGGER TrgMattAudit ON Matt
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;

    INSERT INTO [dbo].[MattAudit]
        ( MattID, [Text], [Action], InsertedDate )
    SELECT
        ISNULL(i.MattID, d.MattID) as MattID,
        ISNULL(i.Text, d.Text) as Text,
        CASE ISNULL(i.MattID,0) WHEN 0 THEN 0 ELSE 1 END
        +
        CASE ISNULL(d.MattID,0) WHEN 0 THEN 0 ELSE -1 END
        as [Action],
        GETDATE() as InsertedDate
    FROM
        inserted i
        INNER JOIN deleted d ON i.MattID = d.MattID;

END

下面的插入语句将向表中插入行，Matt但表中不显示任何内容MattAudit。

INSERT INTO Matt ([Text]) VALUES ('Test4')

我在触发器中遗漏了什么或出错了？

标签： tsqldatabase-triggeraudit-tables

解决方案

I think the problem is because of this:

FROM
    inserted i
    INNER JOIN deleted d ON i.MattID = d.MattID;
  • When inserting you only have records in INSERTED.
  • When you delete records you only have records in DELETED.
  • When you update records you will have records in both INSERTED (new value) and DELETED (old value).

Joining the two will always result in 0 rows for INSERT or DELETE, because when doing an INSERT you will have 1 or more rows in INSERTED, but 0 rows in DELETED. And vice versa for the DELETE statement.

A suggestion I would make is to split the single trigger into a trigger for each situation (INSERT, UPDATE and DELETE) and have a single query in each of your new triggers.

A small caveat is that an AFTER UPDATE trigger will add rows in both INSERTED and DELETED table.

The value in INSERTED will be the value which was put in place, and the value in DELETED will be the old value, before the UPDATE query ran.

推荐阅读