How to send an email from SQL Server using Trigger

See more:

SQL-Server-2005

SQL-Server

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 = 'Emailid@hotmail.com',
@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'EmailId@hotmail.com', @body= @msg,  @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END
GO