SQL Server Trigger switching Insert,Delete,Update

Question

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

Thanks,

Answer 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:

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

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 can cause a trigger to fire multiple times

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.

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

Answer 2

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

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

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

Answer 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.