SQL Server触发器切换插入、删除、更新操作

Question

你好，是否可以在触发器主体之间切换DML命令/操作（插入、删除、更新）？我尝试剪辑一些T-SQL以更好地理解：

CREATE TRIGGER DML_ON_TABLEA
   ON  TABLEA
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    CASE 
    WHEN (INSERT) THEN
        -- INSERT ON AUX TABLEB 
    WHEN (DELETE) THEN
        -- DELETE ON AUX TABLEB 
    ELSE --OR WHEN (UPDATE) THEN
        -- UPDATE ON AUX TABLEB 
    END
END
GO

Thanks,

- Jonathan Escobedo

Answer 1

我会向您展示在 SQL Server 2000 或 2005 中检查这个问题的简单方法（您忘记了提及您使用的版本），但总的来说，我同意 Remus 的观点，即您应该将它们分成单独的触发器：

DECLARE @i INT, @d INT;
SELECT @i = COUNT(*) FROM inserted;
SELECT @d = COUNT(*) FROM deleted;
IF @i + @d > 0
BEGIN
    IF @i > 0 AND @d = 0
    BEGIN
        -- logic for insert
    END

    IF @i > 0 AND @d > 0
    BEGIN
        -- logic for update
    END

    IF @i = 0 AND @d > 0
    BEGIN
        -- logic for delete
    END
END

需要注意的是，由于在SQL Server 2008中引入了MERGE，它引入的复杂性可能导致这种方法不完全向前兼容。有关更多信息，请参见此Connect条目：

• MERGE可能会导致触发器多次触发

因此，如果您计划在未来使用SQL Server 2008和MERGE，那么这更加说明需要将触发器拆分为针对每种DML操作的触发器。

(如果您想要更多避免使用MERGE的理由，请阅读此篇文章和此篇文章。)

- Aaron Bertrand

---

+1 是“拆分触发器的更多理由”。这真的节省了很多时间，而且很“合理”——为什么要费尽心思弄清楚需要做什么，当单独的触发器同样可以完成工作。感谢 @Aaron。 - Matthew M.

这是 Aaron 提到的 Microsoft Connect（现已停用）项目链接：http://connect.microsoft.com:80/SQLServer/feedback/details/321930/merge-can-cause-a-trigger-to-fire-multiple-times - Mark Varnas

@MarkVarnas 我更新了URL。现在你必须通过WayBack机器来访问。 - Aaron Bertrand

Answer 2

您可以使用 inserted 和 deleted 表格 来查看表中所做的更改。

对于 UPDATE 操作，deleted 表格包含行的旧版本，而 inserted 包含新版本。

DELETE 和 INSERT 各自使用自己的表格，与预期相符。

- Andomar

Answer 3

你可以有三个单独的触发器，一个用于INSERT，一个用于UPDATE，一个用于DELETE。由于每个触发器都不同，因此不需要开关逻辑。

- Remus Rusanu

Answer 4

我认为通常的做法是为每个操作创建一个触发器，例如：

CREATE TRIGGER INSERT_ON_TABLEA   
ON  TABLEA   
AFTER INSERT 
AS 
BEGIN    
SET NOCOUNT ON;    
-- INSERT ON AUX TABLEB
END
GO

CREATE TRIGGER DELETE_ON_TABLEA   
ON  TABLEA   
AFTER DELETE
AS 
BEGIN    
SET NOCOUNT ON;    
-- DELETE ON AUX TABLEB
END
GO

- Ryan Elkins

Answer 5

您可以通过使用联合连接为所有命令/操作使用一个触发器；

CREATE TRIGGER DML_ON_TABLEA
 ON  TABLEA
AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
   --logic for insert
    insert into Backup_table (columns_name) select columns_name from inserted i
    --logic for delete
   UNION ALL
    insert into Backup_table (columns_name) select columns_name from deleted d
END
GO

--注意更新命令，类似于插入命令，但有另一个删除命令

- Sung Pham