Wednesday, August 7, 2013

Is DML Trigger Fire When NO ROWS is Effected in a TABLE

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

ms189799.note(en-US,SQL.90).gifNote:
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