鲁泰 ERP 短信平台有个发送短信的 WebService 接口，有时我们希望在往自己的业务表中插入数据的时候发送一条短信，这里分享一种简单的方法，通过触发器和存储过程实现调用 WebService 发送短信。

业务表触发器：

-- =============================================
-- Author:      邢港
-- Create date: 2014.07.22
-- Description: 物流运输管理系统短信通知触发器
-- =============================================

ALTER trigger [dbo].[wlgl_sms_insert] on [dbo].[wlgl_sms] after insert as

declare @tel varchar(50),@nr varchar(500)

declare wlgl_sms_cursor cursor for select tel,dxnr from inserted
    open wlgl_sms_cursor
    fetch next from wlgl_sms_cursor into @tel,@nr
    while @@fetch_status=0
      begin
        --通过存储过程调用短信API WebServices发送短信
        exec SendSMS '短信平台用户名','短信平台密码',@tel,@nr 
      fetch next from wlgl_sms_cursor into @tel,@nr
      end
    close wlgl_sms_cursor 
deallocate wlgl_sms_cursor

调用 WebService 存储过程：

-- =============================================
-- Author:      邢港
-- Create date: 2014.07.22
-- Description: 鲁泰短信平台接口存储过程
-- =============================================

ALTER PROCEDURE [dbo].[SendSMS] @uid varchar(50),@pwd varchar(50),@tel varchar(20),@nr varchar(500)
AS
BEGIN

--exec sp_configure 'show advanced options', 1
--RECONFIGURE
--exec sp_configure 'Ole Automation Procedures', 1
--RECONFIGURE

DECLARE @obj INT
DECLARE @url VARCHAR(3000)
DECLARE @response nVARCHAR(4000)
declare @n nvarchar(100);

SET  @url='http://10.200.0.80:7070/sendsms.aspx?uid='+@uid+'&pwd='+@pwd+'&mobile='+@tel+'&content='+@nr;    

EXEC sp_OACreate 'MSXML2.ServerXMLHttp',@obj out
print @obj
EXEC sp_OAMethod @obj,'Open',null,'GET',@url,false
EXEC sp_OAMethod @obj,'send'
EXEC sp_OAGetProperty @obj,'responseText',@response out
SELECT @response [response]
EXEC sp_OADestroy @obj

END