创建触发器tr_sm_msg


if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_sm_msg]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[tr_sm_msg];

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tr_sm_msg] 
   ON  [dbo].[test]
   AFTER INSERT
AS 
BEGIN

declare @certid varchar(100)
 
    set @certid = (select certid from inserted)
 
    exec proc_useJPushAPI @certid --调用存储过程并传参 如果传入的参数未变则不会触发
 
    SET NOCOUNT ON;


END
GO


存储过程proc_useJPushAPI：

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.proc_useJPushAPI') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
BEGIN
print '存在，删除旧存储过程';
DROP PROCEDURE [dbo].[proc_useJPushAPI];
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[proc_useJPushAPI]
    @certid varchar(100)        --sm_msg表的id字段
AS
BEGIN
 
    declare @url varchar(4000)    --接口路由
    declare @object int            --OLE对象实例
    declare @responseText varchar(4000) --文本
 
    set @url = 'http://192.168.109.110/api/ske/?cert=' + @certid
    
    print @url
 
    exec sp_OACreate'MSXML2.XMLHTTP',@object out
    exec sp_OAMethod @object,'open',null,'get',@url,'true'
    exec sp_OAMethod @object,'send'
    exec sp_OAMethod @object,'responseText',@responseText output
 
    print @responseText
 
    exec sp_OADestroy @object
 
    SET NOCOUNT ON;
 
END

上述exec sp_OAMethod @object,'open',null,'get',@url,'true'中true为非阻塞模式，如果为false为阻塞模式

打开配置文件：
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO