Search code examples
sql-servertriggersdml

SQL Server Trigger switching Insert,Delete,Update

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,

Solution

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

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