How to write trigger body in SQL Server

Question

I'm confused how a trigger in SQL Server knows what event fired the trigger and what to do.

For example I have a trigger that update on table A and it is performing AFTER INSERT, DELETE, UPDATE. Now my question is how I make the body to perform each task when it triggered, do I need to make separate triggers for each task or is it a way to specify what it needs to do for each task on a single body and a single trigger. And if it is okay can anybody give some explanation and example for it

Thanks

Answer 1

If you really must do a single trigger - this is the logic needed to keep the three operation - INSERT, UPDATE and DELETE - apart:

CREATE TRIGGER dbo.YourTriggerName
ON dbo.YourTable
AFTER DELETE, INSERT, UPDATE
AS
BEGIN
    -- check if both the Inserted as well as the Deleted pseudo tables exist
    IF EXISTS (SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted)
    BEGIN
        -- trigger operation : UPDATE
        -- "Inserted" contains the new values for the rows after the update
        -- "Deleted" contains the old values for the rows before the update
    END

    -- check if only the Inserted pseudo tables exists
    IF EXISTS (SELECT * FROM Inserted) AND NOT EXISTS (SELECT * FROM Deleted)
    BEGIN
        -- trigger operation: INSERT
        -- "Inserted" contains the values for the rows inserted 
    END

    -- check if only the Deleted pseudo tables exists
    IF NOT EXISTS (SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted)
    BEGIN
        -- trigger operation: DELETE
        -- "Deleted" contains the values for the rows having been deleted
    END
END;

BE AWARE: the trigger is called once per statement - not once per row - so the Inserted and Deleted pseudo tables will potentially contain multiple rows - handle them in a set-based manner, as tables - don't do anything like

SELECT @ID = i.ID FROM Inserted i

This will NOT WORK for multiple rows being inserted at once in a single SQL statement!

But as I said - this is a bit messy, makes for a really large trigger, and makes it hard to maintain this code. I'd personally would much rather have three separate, focused triggers - one for each of the operations you need to handle.