我已经在一个表上定义了一个触发器,该触发器会在以下情况下被触发:插入、删除和更新。有时候触发器会在
INSERTED
和DELETED
表都为空的情况下触发。这是怎么可能的呢?对于记录来说,以下是触发器的代码。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