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?

http://msdn.microsoft.com/en-us/library/ms189799%28SQL.90%29.aspx

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

     

Hope you like it.

Posted by: MR.JOYDEEP DAS