CODEWITHSUNDEEP
sql-servert-sqltriggers
Kaspar Kjeldsen
Kaspar Kjeldsen

Reputation: 992

SQL Server Trigger fires unexplainably

I've got a trigger on a table. It's a very simple trigger, set to after insert, send me an e-mail. Since I've put that trigger on, I've been sent e-mails by the system every 5 or 6 minutes or so. There is just one problem. Whenever I receive an e-mail, the table is EMPTY

Here is my trigger

USE [didaprod]
GO
/****** Object:  Trigger [dbo].[Caseplayer_CaseId_Restore_insert_mail]    Script Date: 09-08-2016 11:59:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE trigger [dbo].[Caseplayer_CaseId_Restore_insert_mail] on [dbo].[Caseplayer_CaseId_Restore]

AFTER INSERT
as

    SET NOCOUNT ON

    declare @tekst nvarchar(500);

    set @tekst = 'caseid sat til null på caseplayer! Tjek Caseplayer_CaseId_Restore tabel!' + convert(varchar,getdate(),105);

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail',
                                @recipients = '[email protected]',
                                @subject = 'CASEID SAT TIL NULL!!!',
                                @body = @tekst

    SET NOCOUNT OFF
GO

I've tried to manually insert a row, or a couple of rows in the table, just to check, and yes, the trigger fires as well, when there is a proper insert. But I cannot explain why I keep receiving the e-mails!. As for the table itself, it's got nothing fancy.

USE [didaprod]
GO

/****** Object:  Table [dbo].[Caseplayer_CaseId_Restore]    Script Date: 09-08-2016 12:04:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Caseplayer_CaseId_Restore](
    [Id] [int] NOT NULL,
    [CaseId] [int] NOT NULL,
    [TimeOfChange] [datetime] NOT NULL
) ON [PRIMARY]

GO

I've disabled the trigger now, and confirmed that the e-mails have stopped. But this seems like an almost magic problem for me, and I would love to get an answer.

Suppose I do

delete from [Caseplayer_CaseId_Restore]

And just leave the table alone. I'll still get e-mails from the system, telling me to check the table. When I then perform a 

select * from [Caseplayer_CaseId_Restore]

The table is empty

Upvotes: 1

Views: 36

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

SQL Server triggers fire once per statement rather than once per row. That's why it's important to write triggers that use inserted appropriately, to deal with the fact that it may contain 1, many, or zero rows.

I suspect that it's the latter case here. (I.e. a regularly executed INSERT statement that is in fact inserting zero rows)

So, you might want something like:

CREATE trigger [dbo].[Caseplayer_CaseId_Restore_insert_mail]
            on [dbo].[Caseplayer_CaseId_Restore]

AFTER INSERT
as

    SET NOCOUNT ON

    IF EXISTS(select * from inserted)
    BEGIN
      declare @tekst nvarchar(500);

      set @tekst = 'caseid sat til null på caseplayer! Tjek Caseplayer_CaseId_Restore tabel!' + convert(varchar,getdate(),105);

      EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail',
                                @recipients = '[email protected]',
                                @subject = 'CASEID SAT TIL NULL!!!',
                                @body = @tekst

    END

Upvotes: 3

Related Questions