If you really must do a single trigger – this is the logic needed to keep the three operation –
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
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.