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

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.

Answer 2

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

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.

Answer 3

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 INSERT 
AS 
BEGIN    
SET NOCOUNT ON;    
-- DELETE ON AUX TABLEB
END
GO

Answer 4

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.