how can i write a trigger body in sql server?

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.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top