SQL Server Trigger switching Insert,Delete,Update

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

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.

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.