[转]create a basic sql server 2005 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
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
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!
另:
Database Mail and SQL Mail Stored Procedures (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms177580(v=sql.90).aspx
最新文章
- Google疯了,竟然这样!
- 微信支付Native扫码支付模式二之CodeIgniter集成篇
- Codeforces Round #383 (Div. 2) D 分组背包
- php 截取中文字符串 - ord()函数 0xa0...
- LLDB, iOS调试器
- UIWebView 操作
- Linux常用命令_(安装包管理)
- 【转】github更新自己Fork的代码
- 2075 yh女朋友的危机、2544 拯救小矮人
- 输入参数varargin
- centos扩容(pv,vg,lv)
- mac下 redis安装使用
- (五):C++分布式实时应用框架——微服务架构的演进
- hdu 2112 map+Dijkstra
- db2笔记
- java监控指定路径下文件及文件夹变化
- Linux 配置selenium + webdriver 环境
- 关于jQuery Form Plugin使用心得
- GeSHi——通用语法高亮显示
- 让Android 变身回一台真正的Linux系统吧!!!