SQL Trigger Does Not Work When Trying to Have Trigger Send A Email

I am trying to create a database trigger that fires off when a new row is inserted which has a specific word it will send an email alert. The email alert works when I just run that code and the trigger works if I remove the email piece and replace it with something else.

Here is the code:

ALTER TRIGGER CheckForDirty
ON dbo.Messages
FOR INSERT,UPDATE
AS

DECLARE @MESSAGETEXT varchar(max)
DECLARE @BADWORD1 varchar(50)

SET @MESSAGETEXT = (SELECT Body FROM inserted)
SET开发者_开发知识库 @BADWORD1 = 'BadWord'

IF CHARINDEX(@BADWORD1, @MESSAGETEXT) > 0
BEGIN

    EXEC msdb.dbo.sp_send_dbmail @recipients='message@email.com',
    @subject = 'Test e-mail sent from database mail',
    @body = 'Someone said something dirty'

END
GO

You would be well advised to decouple the email sending code from the trigger - in the trigger, insert the information you need into a "Pending Emails" table. Then create a job that periodically checks the table and send email.

You also need to re-write your trigger to cope with multi-row inserts and updates. inserted is a pseudo table that can contain multiple rows.

So I'd write a trigger something like this:

CREATE TRIGGER CheckForDirty
ON dbo.Messages
FOR INSERT,UPDATE
AS

INSERT INTO PendingEmails (Recipients,Subject,Body)
SELECT 'message@email.com','Test e-mail sent from database mail','Someone said something dirty'
FROM inserted
WHERE CHARINDEX('BadWord',Body) > 0

Although I'm assuming that the actual body of the message you're constructing may rely on more info to help identify which message is implicated.

Okay, if we're going to build this out into a full example, we'll need to define PendingEmails:

CREATE TABLE PendingEmails (
    PendingEmailID int IDENTITY(1,1) not null,
    Recipients varchar(max) not null,
    Subject nvarchar(255) not null,
    Body nvarchar(max) not null,
    constraint PK_PendingEmails PRIMARY KEY (PendingEmailID)
)

And now we'll write a stored proc that can process pending emails:

CREATE PROCEDURE DispatchPendingEmails
AS
     declare @PendingEmailID int
     declare @Recipients varchar(max)
     declare @Subject nvarchar(255)
     declare @Body nvarchar(max)

     while exists(select * from PendingEmails)
     begin
         select top 1 @PendingEmailID = PendingEmailID,@Recipients = Recipients,
             @Subject = Subject, @Body = Body from PendingEmails

         exec msdb.dbo.sp_send_dbmail @recipients = @Recipients, @subject = @Subject, @body = @Body

         delete from PendingEmails where PendingEmailID = @PendingEmailID
     end

And finally, we need to create the job. I'd normally do this through SSMS, but if you want it scripted (you'll need to substitute a different database name in the sp_add_jobstep line, and this is set to run the dispatch every five minutes. All of the stored procs are well documented in MSDN):

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DispatchPendingEmails', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_netsend=2, 
        @notify_level_page=2, 
        @delete_level=0, 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DispatchPendingEmails', @server_name = N'SYSTEMS86\SQL2K8'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DispatchPendingEmails', @step_name=N'ExecSP', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_fail_action=2, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec DispatchPendingEmails', 
        @database_name=N'Database', --<-- This needs editing
        @flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DispatchPendingEmails', 
        @enabled=1, 
        @start_step_id=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_netsend=2, 
        @notify_level_page=2, 
        @delete_level=0, 
        @description=N'', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', 
        @notify_email_operator_name=N'', 
        @notify_netsend_operator_name=N'', 
        @notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DispatchPendingEmails', @name=N'QuiteOften', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=5, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20110218, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

First guess would be permissions. When you run the email send manually are you running under a different account than when you execute the trigger? I have seen this before where you test the code under your user account then run an application under a different account that has less permissions. You need to make sure all users are in the correct mail group.