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.