使用空插入和删除表的Sql服务器触发触发器。

I have defined a trigger on a table that is triggered

我已经在被触发的表上定义了一个触发器

AFTER INSERT, DELETE, UPDATE

后插入、删除、更新

There are cases where the trigger fires, with both INSERTED AND DELETED tables being empty. How can this be possible?

在某些情况下，触发器触发，插入和删除的表都是空的。这怎么可能呢?

For the records, that's the trigger

对于记录，这是触发器

CREATE TRIGGER [dbo].[AuditUsersTrigger] ON [dbo].[Users]
AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
    SET NOCOUNT ON


    DECLARE @type nchar(1), @hasChanges bit
    SET @hasChanges = 1
    IF EXISTS (SELECT * FROM INSERTED)
        IF EXISTS (SELECT * FROM DELETED)
        BEGIN
            SELECT @type = 'U'

            IF EXISTS (
                SELECT *
                FROM INSERTED i
                INNER JOIN DELETED d ON
                    i.Name = d.Name AND
                    i.Pwd = d.Pwd AND
                    ...
            ) SELECT @hasChanges = 0
        END
        ELSE
            SELECT @type = 'I'
    ELSE
        SELECT @type = 'D'


    IF @type = 'D' OR (@type = 'U' AND @hasChanges = 1)
    BEGIN
        INSERT AuditUsers (
            New, Id, Name, ...
        )
        SELECT
            0, Id, Name, ...
        FROM DELETED

        IF @type = 'D'
        BEGIN
            INSERT AuditUsers (New)
            SELECT 1
        END
    END

    IF @type = 'I' OR (@type = 'U' AND @hasChanges = 1)
    BEGIN
        IF @type = 'I'
        BEGIN
            INSERT AuditUsers (New)
            SELECT 0
        END

        INSERT AuditUsers (
            New, Id, Name, ...
        )
        SELECT
            0, Id, Name, ...
        FROM INSERTED
    END


    IF Trigger_Nestlevel() < 2
    BEGIN
        DECLARE @clientId TABLE (id INT)
        DECLARE @clientCode NVARCHAR(50), @shopId INT;

        IF @type = 'I' OR @type = 'U'
        BEGIN
            SELECT @clientCode = ClientCode, @shopId = ShopId FROM INSERTED;
            INSERT INTO @clientId SELECT id FROM Clients WHERE code = @clientCode;

            IF NOT EXISTS (SELECT 1 FROM @clientId)
            BEGIN
                INSERT Clients (name, code, active, shopId) OUTPUT INSERTED.id INTO @clientId
                VALUES (@clientCode, @clientCode, 1, @shopId);
            END


            UPDATE Users SET ClientId = (SELECT TOP 1 id FROM @clientId) WHERE ClientCode = @clientCode;
        END
    END
END

1 个解决方案

#1

This is documented behaviour

这是记录的行为

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

当用户试图通过数据操作语言(DML)事件修改数据时，DML触发执行。DML事件是表或视图上的插入、更新或删除语句。当触发任何有效事件时，不管是否影响任何表行，这些触发器都会触发。

If you have a recurring loop, whereby table A has a trigger that affects table B, and table B has a trigger that affects table A, you can manage this using TRIGGER_NESTLEVEL, or by checking if either inserted or deleted contain any rows before actually doing anything.

如果您有一个循环循环，其中表a有一个影响表B的触发器，表B有一个影响表a的触发器，您可以使用TRIGGER_NESTLEVEL来管理它，或者在实际执行任何操作之前检查插入或删除是否包含任何行。