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.