异步触发器发送短信

ServiceBroker的队列存在自动激活（ACTIVATION）功能，其中内部激活可以激活数据库存储过程接受和处理队列的消息，而且可以启动激活存储过程的多个实例（MAX_QUEUE_READERS）。当SQLServer的SCHEDULER个数大于1（即多CPU）时，会有多个实例同时去接受并处理消息。

业务流程：

1）、将短信内容放到待发送表（如：sms_sends），触发器发送到队列

2）、SP遍历队列，调用接口发送。

启用SQL Server Service Broker 

 

ALTER DATABASE hyMall SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

GO

ALTER DATABASE hyMall SET ENABLE_BROKER;

GO

ALTER DATABASE hyMall SET TRUSTWORTHY ON; --设置信任该数据库，不然有些SP无法执行

USE hyMall;

GO

 

 

 

--创建消息类型。使用时，可替换所有My__MSG名称。

create message type My__MSG

--创建约定

create contract My__MSG_contract

(

    My__MSG sent by initiator

)

--创建客户端队列

create queue My__MSG_client_queue

--创建客户端服务

create service My__MSG_client on queue My__MSG_client_queue;

--创建库存队列

create queue My__MSG_queue

--创建库存更新服务

create service My__MSG

on queue My__MSG_queue([My__MSG_contract]);

 

 

 

触发器

ALTER TRIGGER [dbo].[触发器名称]

   ON  [dbo].[表名]

   AFTER INSERT

AS

BEGIN

 

    SET NOCOUNTON;

        declare@mobile varchar(50)

        declare@msg varchar(50)

        select @m=mobile,@n=msg From inserted

       

    declare @dialog_id uniqueidentifier

    begin dialog conversation @dialog_id

        FROM service My__MSG_client

        TO   service 'My__MSG'

        ON CONTRACT [My__MSG_contract]

        with encryption = off;

        SEND

        on conversation @dialog_idmessage type My__MSG

        (

        '<xml>

        <mobile>'+@mobile+'</mobile>

        <msg>'+@msg+'</msg>

        </xml>'   

        );

END

 

 

存储过程

 

--存储过程

ALTER proc [dbo].[My__MSG_Proc]

as

 

    declare @dialog_id2 uniqueidentifier

    declare @message xml

    declare @mobile nvarchar(50);

    declare @msg nvarchar(500);

   

    while(1=1)

    begin

                    --begintransaction

                  waitfor(receive @dialog_id2 = conversation_handle,@message = message_body from [dbo].My__MSG_queue),timeout 5000;

                

                  if(@dialog_id2 is not null)

                  begin

                     set @mobile = @message.value('(/xml/mobile)[1]','nvarchar(50)');

                     set @msg = @message.value('(/xml/msg)[1]','nvarchar(500)');

                     --- 业务操作

                         Declare @URLVARCHAR(200)

Set @URL=’http://www.baiud.com/?mobile=’+@mobile

                      EXEC HTTP@URL

                     ---

                         end conversation @dialog_id2;

 

                  end

                  IF (@@ROWCOUNT = 0) break;

                    --committransaction;

    end

 

 

启用队列

---启用队列的自动激活功能，激活的存储过程为上面创建的存储，设置最大读取器个数（并发）为.

 ALTER QUEUE [dbo].[My__MSG_queue]

 WITH STATUS = ON ,

 RETENTION = OFF ,

 ACTIVATION (

               STATUS = ON ,

               PROCEDURE_NAME = [dbo].[My__MSG_Proc] ,

               MAX_QUEUE_READERS = 1,

               EXECUTE AS N'dbo'

           ),

 POISON_MESSAGE_HANDLING(STATUS = OFF)

 

 

 

 

 

 

当MAX_QUEUE_READERS不为1时，多个存储过程实例会同时并发进行，如果涉及库存问题，会造成库存信息不准确。

注：最终库存数量不一定为-3,需视Scheduler数量以及同时有多少个存储过程实例可以获得CPU来执行有关

解决方法：
一种是设置队列的自动激活的最大实例数为1，即不允许并发读取
另外就是在读取库存时，增加提示 with(holdlock)，这样只允许一个实例读取库存表的一行数据，直到事务结束。

 

 

ALTER proc [dbo].[HTTP]

  @url varchar(2000)

as

 

declare @ServiceUrl varchar(500)

set @ServiceUrl=@url

Declare @Object as Int

Declare @ResponseText as Varchar(8000)

Exec sp_OACreate 'MSXML2.XMLHTTP', @ObjectOUT;

Exec sp_OAMethod @Object, 'open', NULL,'get',@ServiceUrl,'false'

Exec sp_OAMethod @Object, 'send'

Exec sp_OAMethod @Object, 'responseText',@ResponseText OUTPUT

Select @ResponseText    

Exec sp_OADestroy @Object

GO