插入时触发电子邮件 [英] email trigger on insert

问题描述

我只想在输入了某些字段的情况下发送电子邮件.在这种情况下@lab =``是''
我收到任何插入的电子邮件.

I want to only send email if certain fields ar entered. In this case @lab = ''Yes''
I am getting emails on any insert.

CREATE TRIGGER [Furture Attentionl] ON dbo.MasterUser 

FOR INSERT 
AS
declare @FirstName nchar(11), @LastName nchar(20), 
@RequestingDepartment nchar(25), @RequestingManager nchar(20), @EMPStrDate nchar(11), @Lab nchar(11), @TextMessage nchar(2020)

select @FirstName = FirstName, @LastName  = LastName,
@RequestingDepartment = RequestingDepartment, @RequestingManager  = RequestingManager, @EmpStrDate = EMPStrDate, @Lab = Lab 
From Inserted

where @lab = 'Yes'


set @TextMessage = N'Name: ' + @FirstName + @LastName + nchar(13) + nchar(10)+
+ N'Dept: ' + @RequestingDepartment + nchar(13) + nchar(10) +
+ N'Manager: ' + @RequestingManager + nchar(13) + nchar(10) +
+ N'Emp Start Date: ' +@EMPStrDate + nchar(13) + nchar(10) +
 + N'KRMC Employee: ' +@Lab + nchar(13) + nchar(10)




declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail @server = N'vexchange.azkrmc.org',
    @FROM   = N'[e-mail]',
    @TO     = N'[e-mail] ' ,  
    
    
    @subject = N'Request Further Attention',
    @message = @TextMessage 

select RC = @rc

推荐答案

第一件事:

select @FirstName = FirstName, @LastName = LastName,
@RequestingDepartment = RequestingDepartment, @RequestingManager = RequestingManager, @EmpStrDate = EMPStrDate, @Lab = Lab 
From Inserted
where @lab = 'Yes'

应为

select @FirstName = FirstName, @LastName = LastName,
@RequestingDepartment = RequestingDepartment, @RequestingManager = RequestingManager, @EmpStrDate = EMPStrDate, @Lab = Lab 
From Inserted
 where lab = 'Yes'

然后，您只需执行以下操作:

Then, you can simply do the following:

if @lab='Yes'
BEGIN
set @TextMessage = N'Name: ' + @FirstName + @LastName + nchar(13) + nchar(10)+
+ N'Dept: ' + @RequestingDepartment + nchar(13) + nchar(10) +
+ N'Manager: ' + @RequestingManager + nchar(13) + nchar(10) +
+ N'Emp Start Date: ' +@EMPStrDate + nchar(13) + nchar(10) +
+ N'KRMC Employee: ' +@Lab + nchar(13) + nchar(10)
 
 
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail @server = N'vexchange.azkrmc.org',
@FROM = N'[e-mail]',
@TO = N'[e-mail] ' , 

@subject = N'Request Further Attention',
@message = @TextMessage 
 
select RC = @rc
END

这篇关于插入时触发电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！