如何使用触发器从SQL Server发送电子邮件 [英] How to send an email from SQL Server using Trigger

问题描述

亲爱的所有人，

我正在使用SQL Server 2005&在清单表中插入记录时无法发送电子邮件.它显示在清单表中插入记录后，邮件已排队.

请让我尽快了解解决方案.

注意:当我在下面的查询中执行时，在send_status列中显示未发送".

Dear All,

I am using SQL Server 2005 & it''s unable to send an email when I am inserting a record in the inventory table. It''s showing Mail queued after insert a record in the inventory table.

Please let me know the solution as early as possible.

Note: It''s showing ''unsent'' in the sent_status column when I am execute below query.

" SELECT 'sysmail_allitems' as TableName, * FROM msdb.dbo.sysmail_allitems "

我将查询编写如下:

I write the queries as follows:

--Enable SQL SMail
use PracticeDB
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBAC',
@email_address = '[e-mail]',
@display_name = 'Mohammed Abdul Mohsin',
@mailserver_name = 'mail.hotmail.com'

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBPN'

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBPN',
@account_name = 'DBAC',
@sequence_number = 1 ;

--Table

CREATE TABLE dbo.inventory (
item varchar(50),
price money
)
GO

--Trigger for Inventory Table

CREATE TRIGGER Trg_Inventory ON dbo.inventory AFTER INSERT AS
DECLARE @price money
DECLARE @item varchar(50)
SET @price  = (SELECT price FROM inserted)
SET @item = (SELECT item FROM inserted)
IF @price >= 1000
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'
EXEC msdb.dbo.sp_send_dbmail @recipients=N'[e-mail]', @body= @msg,  @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END
GO

推荐答案

' + CAST( as 10 ))+ ' .' EXEC msdb.dbo.sp_send_dbmail @ recipients = N ' EmailId @ hotmail.com'，@ body = @ msg ， @ subject = ' SQL Server触发邮件'， @ profile_name = ' DBMailProfile' END GO

' + CAST(@price as varchar(10)) + '.' EXEC msdb.dbo.sp_send_dbmail @recipients=N'[e-mail]', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile' END GO

检查服务代理是否正在实际运行.例如，尝试使用 sysmail_start_sp [ ^ ]

Check that the service broker is actually running. For example try staring the broker using sysmail_start_sp[^]

这篇关于如何使用触发器从SQL Server发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！