SQL Server触发器切换插入，删除，更新 [英] SQL Server Trigger switching Insert,Delete,Update

问题描述

您可以在触发器主体上的DML命令/操作（插入，删除，更新）之间切换吗？，我尝试摘录一些T-SQL以更好地理解我：

Hello is possible to switch between DML commands/operations (Insert,Delete,Update) on Trigger Body?, I try to snippet some T-SQL for understand me better :

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

谢谢

推荐答案

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

I will show you a simple way to check this in SQL Server 2000 or 2005 (you forgot to mention which version you are using), but in general I agree with Remus that you should break these up into separate triggers:

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项目：

Note that this may not be perfectly forward-compatible due to the complexity MERGE introduces in SQL Server 2008. See this Connect item for more information:

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

• MERGE can cause a trigger to fire multiple times

因此，如果您打算将来使用SQL Server 2008和 MERGE ，那么这是

So if you are planning to use SQL Server 2008 and MERGE in the future, then this is even more reason to split the trigger up into a trigger for each type of DML operation.

（而且如果您想更多理由避免 MERGE ，阅读此内容。）

(And if you want more reasons to avoid MERGE, read this.)

这篇关于SQL Server触发器切换插入，删除，更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！