//
you're reading...
SQL Server

Create a Basic SQL Server to Send E-mail Alerts

Ever have a need to send an email alert from a trigger but could never quite figure out how to do it. The following code will fire an email alert from a trigger.

Database Mail is not active by default. To use Database Mail, you must explicitly enable Database Mail by using either the Database Mail Configuration Wizard, the sp_configure stored procedure, or by using the Surface Area Configuration facet of Policy-Based Management.

– enable dbmail via sp_configure

use master
go
sp_configure ’show advanced options’,1
go
reconfigure with override
go
sp_configure ‘Database Mail XPs’,1
go
reconfigure
go

– Create a default mail profile                             –CHANGE @email_address,@display_name and @mailserver_name VALUES to support your environment

 

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘DBMailAccount’,
@email_address = ’sqlserver@domain.com’,
@display_name = ‘SQL Server Mailer’,
@mailserver_name = ‘exchangeServer’

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘DBMailProfile’

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘DBMailProfile’,
@account_name = ‘DBMailAccount’,
@sequence_number = 1 ;

 

–Now that everything is set up, send a test e-mail. 

SET

 

@Body=‘New dbmail config successfully created on ‘+CAST(SERVERPROPERTY(‘ServerName’) AS VARCHAR(50))

 

EXEC msdb.dbo.sp_send_dbmail

@recipients=N’me@mydomain.com’,

@body= @Body ,

@subject= ‘dbmail Test Email’,

@profile_name= ‘DBMailProfile’

 

–To see if the message was sent successfully.

SELECT * FROM sysmail_allitems

 

–Now that you know that dbmail is working you can build your trigger.

CREATE TRIGGER SampleTriggerName ON SampleTableName 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)) + ‘.’
–CHANGE THE VALUE FOR @recipients
 EXEC msdb.dbo.sp_send_dbmail @recipients=N’manager@domain.com’, @body= @msg,  @subject = ‘SQL Server Trigger Mail’, @profile_name = ‘DBMailProfile’
END
GO

 

To test simply insert a row into your test table and watch the alert appear in your in box. This approach can be used for any type of trigger you want; (for, after,instead of)intert, update, delete.

 
Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

Comments are closed.

%d bloggers like this: