SQL Server触发器切换插入，删除，更新

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 :

你好可以在Trigger Body上的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,

谢谢，

4 个解决方案

#1

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:

我将向您展示一种在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
        -- insert
    IF @i > 0 AND @d > 0
        -- update
    IF @i = 0 AND @d > 0
        -- delete
END

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: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=321930

请注意，由于MERGE在SQL Server 2008中引入的复杂性，这可能无法完全向前兼容。有关详细信息，请参阅此Connect项：http：//connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx ?FeedbackID = 321930

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.

因此，如果您计划将来使用SQL Server 2008和MERGE，那么这更有理由将触发器分解为每种类型的DML操作的触发器。

#2

You can use the inserted and deleted tables to see what changes were made to the table.

您可以使用inserted和deleted表来查看对表进行了哪些更改。

For an UPDATE, the deleted table contains the old version of the row, and inserted the new version.

对于UPDATE，已删除的表包含该行的旧版本，并插入新版本。

DELETE and INSERT use their own table as you would expect.

DELETE和INSERT按照您的预期使用自己的表。

#3

You can have three separate triggers, one for INSERT one for UPDATE one for DELETE. Since each trigger is different, there is no need for switch logic.

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

#4

I think the general way to do this is to create a trigger for each action, like so:

我认为执行此操作的一般方法是为每个操作创建一个触发器，如下所示：

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

#1

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
        -- insert
    IF @i > 0 AND @d > 0
        -- update
    IF @i = 0 AND @d > 0
        -- delete
END