SQL 触发器 - 如何测试操作？

Question

我的触发器触发 INSERT, UPDATE and DELETE .我需要根据触发触发器的操作从适当的内存表( inserted, deleted )插入。由于只有 inserted位于 INSERT或 deleted在 DELETE我想我可以做一个插入，如果没有行，我很好。

但是，UPDATE填充两个表。我只想要来自 deleted 的值在 UPDATE .我尝试使用 UPDATE(column) 测试更新函数，但这会返回 TRUE即使在 INSERT .那么，我如何测试 UPDATE ?

ALTER TRIGGER CREATE_tableAudit
   ON dbo.table
   FOR INSERT, UPDATE, DELETE
AS 
BEGIN          
    IF(UPDATE([column1]))--returns true on INSERT :(
        BEGIN
            INSERT INTO [dbo].[tableAudit]
               ([column1]
               ,[CreateDate]
               ,[UpdateDate])
               SELECT * from deleted --update
        END
    ELSE
        BEGIN
        --only inserted is populated on INSERT, visa-versa with DELETE
        INSERT INTO [dbo].[tableAudit]
               ([column1]
               ,[CreateDate]
               ,[UpdateDate])
               select * from inserted --insert


        INSERT INTO [dbo].[tableAudit]
               ([column1]
               ,[CreateDate]
               ,[UpdateDate])
               select * from deleted --delete

        END

Answer 1

要测试 UPDATE，请在 Inserted 中查找相同的主键值。和 Deleted表。

/* These rows have been updated */
SELECT i.PKColumn
    FROM inserted i
        INNER JOIN deleted d
            ON i.PKColumn = d.PKColumn