SQL Server: trigger for insert update and delete caught when no data been changed?
You are relying on the presence of rows in either inserted
or deleted
(or both). What happens when no rows are affected? The trigger still fires.
CREATE TABLE dbo.floob(a int);
INSERT dbo.floob(a) VALUES(1);
GO
CREATE TRIGGER dbo.TRfloob
ON dbo.floob
FOR INSERT, UPDATE, DELETE
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM inserted)
AND NOT EXISTS (SELECT 1 FROM deleted)
BEGIN
PRINT 'Strange, unknown operation!';
END
END
GO
UPDATE dbo.floob SET a = 2 WHERE a = 2;
GO
To avoid this kind of thing, typically people start their trigger with something like:
IF NOT EXISTS (SELECT 1 FROM inserted)
AND NOT EXISTS (SELECT 1 FROM deleted)
BEGIN
RETURN;
END
Checking @@ROWCOUNT
is popular too, but I find it far more brittle.
Question: Should you be taking any action against the database if no changes are made? This seems like it will needlessly slow down your application, and is not best practice.
I would say here that a better solution would be to remove the unnecessary database transactions, if at all possible.
If this trigger is meant to audit in any way (which it looks like it is) then @Aaron Bertrand's answer will not log any updates to the audit table.