Is DML Trigger Fire When NO ROWS is Effected in a TABLE
Introduction
One of my friends has a question that is the Trigger fire if there is no row affect in the table.
My Answer is
YES it is
What MS says about it?
From Microsoft Developer Network
 Note: |
These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.This is by design.
|
So it says that the Trigger fires with any valid event are fires. It not depends on whether the event affects any row or not.
To Demonstrate This
Here we take a simple Example
IF OBJECT_ID(N'dbo.Tbl_EXAMPLE', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Tbl_EXAMPLE;
END
GO
CREATE TABLE dbo.Tbl_EXAMPLE
(ID INT NOT NULL IDENTITY PRIMARY KEY,
NAME VARCHAR(50) NOT NULL);
GO
INSERT INTO dbo.Tbl_EXAMPLE
(NAME)
VALUES ('Joydeep Das'),('Sangram Jit'),('Madhurima Das'),('Chandan Bhattacarys');
GO
IF OBJECT_ID(N'dbo.trg_DELETEREC', N'TR') IS NOT NULL
BEGIN
DROP TRIGGER dbo.trg_DELETEREC;
END
GO
CREATE TRIGGER dbo.trg_DELETEREC
ON dbo.Tbl_EXAMPLE
AFTER INSERT, DELETE
AS
BEGIN
PRINT 'Trigger Effected';
END
DELETE Tbl_EXAMPLE WHERE ID=10
The above DELETE statement not affects any rows in the table. But the Trigger is fired.
How to Solve it
To solve it we have to change the definition of Trigger little more.
IF OBJECT_ID(N'dbo.trg_DELETEREC', N'TR') IS NOT NULL
BEGIN
DROP TRIGGER dbo.trg_DELETEREC;
END
GO
CREATE TRIGGER dbo.trg_DELETEREC
ON dbo.Tbl_EXAMPLE
AFTER INSERT, DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM inserted)
BEGIN
PRINT 'Trigger Effected';
END
IF EXISTS(SELECT * FROM deleted)
BEGIN
PRINT 'Trigger Effected';
END
END
