Insert Update trigger how to determine if insert or update
Many of these suggestions do not take into account if you run a delete statement that deletes nothing.
Say you try to delete where an ID equals some value that does not exist in the table.
Your trigger still gets called but there is nothing in the Deleted or Inserted tables.
Use this to be safe:
--Determine if this is an INSERT,UPDATE, or DELETE Action or a "failed delete".
DECLARE @Action as char(1);
SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
THEN 'U' -- Set Action to Updated.
WHEN EXISTS(SELECT * FROM INSERTED)
THEN 'I' -- Set Action to Insert.
WHEN EXISTS(SELECT * FROM DELETED)
THEN 'D' -- Set Action to Deleted.
ELSE NULL -- Skip. It may have been a "failed delete".
END)
Special thanks to @KenDog and @Net_Prog for their answers.
I built this from their scripts.
I'm using the following, it also correctly detect delete statements that delete nothing:
CREATE TRIGGER dbo.TR_TableName_TriggerName
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM INSERTED)
-- DELETE
PRINT 'DELETE';
ELSE
BEGIN
IF NOT EXISTS(SELECT * FROM DELETED)
-- INSERT
PRINT 'INSERT';
ELSE
-- UPDATE
PRINT 'UPDATE';
END
END;
Triggers have special INSERTED
and DELETED
tables to track "before" and "after" data. So you can use something like IF EXISTS (SELECT * FROM DELETED)
to detect an update. You only have rows in DELETED
on update, but there are always rows in INSERTED
.
Look for "inserted" in CREATE TRIGGER.
Edit, 23 Nov 2011
After comment, this answer is only for INSERTED
and UPDATED
triggers.
Obviously, DELETE triggers can not have "always rows in INSERTED
" as I said above
CREATE TRIGGER dbo.TableName_IUD
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
--
-- Check if this is an INSERT, UPDATE or DELETE Action.
--
DECLARE @action as char(1);
SET @action = 'I'; -- Set Action to Insert by default.
IF EXISTS(SELECT * FROM DELETED)
BEGIN
SET @action =
CASE
WHEN EXISTS(SELECT * FROM INSERTED) THEN 'U' -- Set Action to Updated.
ELSE 'D' -- Set Action to Deleted.
END
END
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED) RETURN; -- Nothing updated or inserted.
...
END