SQL Server[转]create a basic sql server 2007 trigger to send email alerts

本文转自:http://blog.netnerds.net/2008/02/create-a-basic-sql-server-2005-trigger-to-send-e-mail-alerts/

 

For as many times as I have read about sending e-mails using SQL Server
triggers, I’ve rarely come across actual code samples. After someone
asked for a “Triggers for Dummies” example in a Facebook SQL group, I
created the following example which uses a trigger to alert a manager
that an expensive item has been entered into inventory.

First, if SQL Mail isn’t enabled and a profile hasn’t been created, we
must do so.

--// First, enable SQL SMail

use master

go

 

sp_configure 'show advanced options',1

go

 

reconfigure with override

go

 

sp_configure 'Database Mail XPs',1

go

 

reconfigure

go--//Now create the 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 SQL will support sending e-mails, let’s create the sample
table.

This is not a useful or well designed table by any means — it’s just a
simple example table:

CREATE TABLE dbo.inventory (

item varchar(50), price money

)

GO

Now that SQL mail and the table are setup, we will create a trigger that
does the following:

  • Creates an AFTER INSERT trigger named expensiveInventoryMailer on
    the inventory table.

       This means that the trigger will be executed after the data has
been entered.

  • Checks for items being entered that have a price of $1000 or more
  • If there is a match, an email is sent using the SQL Mail profile we
    used above.

CREATE TRIGGER expensiveInventoryMailer

ON dbo.inventory AFTER INSERT

ASDECLARE @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

The only way to test a trigger is to add actual data, so let’s do that
here:

insert into inventory (item,price) values (‘Vase’,100)

insert into inventory (item,price) values (‘Oven’,1000)

 

Your email should arrive very quickly.

If it doesn’t, check the SQL Server mail log in SQL Management Studio by
running SELECT * FROM sysmail_allitems.

Have fun!

SQL Server, 

 

 

另:

Database Mail and SQL Mail Stored Procedures (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms177580(v=sql.90).aspx

 

相关文章