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

i trying create database trigger fires off when new row inserted has specific word send email alert. email alert works when run code , trigger works if remove email piece , replace else.

here code:

alter trigger checkfordirty on dbo.messages insert,update  declare @messagetext varchar(max) declare @badword1 varchar(50)  set @messagetext = (select body 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 database mail',     @body = 'someone said dirty'  end go 

you advised decouple email sending code trigger - in trigger, insert information need "pending emails" table. create job periodically checks table , send email.

you need re-write trigger cope multi-row inserts , updates. inserted pseudo table can contain multiple rows.

so i'd write trigger this:

create trigger checkfordirty on dbo.messages insert,update  insert pendingemails (recipients,subject,body) select 'message@email.com','test e-mail sent database mail','someone said dirty' inserted charindex('badword',body) > 0 

although i'm assuming actual body of message you're constructing may rely on more info identify message implicated.

---

okay, if we're going build out full example, we'll need 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 we'll write stored proc can process pending emails:

create procedure dispatchpendingemails      declare @pendingemailid int      declare @recipients varchar(max)      declare @subject nvarchar(255)      declare @body nvarchar(max)       while exists(select * pendingemails)      begin          select top 1 @pendingemailid = pendingemailid,@recipients = recipients,              @subject = subject, @body = body pendingemails           exec msdb.dbo.sp_send_dbmail @recipients = @recipients, @subject = @subject, @body = @body           delete pendingemails pendingemailid = @pendingemailid      end 

and finally, need create job. i'd through ssms, if want scripted (you'll need substitute different database name in sp_add_jobstep line, , set run dispatch every 5 minutes. of stored procs 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', --<-- 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