SQLServer数据库 用触发器调用接口

一、激活数据库存储过程（默认是不激活的）

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

二、编写触发器

CREATE TRIGGER [dbo].[触发器名称]
ON [dbo].[表名]
AFTER insert
AS
BEGIN
      declare @msgId int
      set @msgId = (select id from inserted)
      exec 存储过程名称 @msgId --调用存储过程并传参如果传入的参数未变则不会触发
      SET NOCOUNT ON;
END

三、编写存储过程

CREATE PROCEDURE [dbo].[存储过程名称]
      @msgId varchar(100)           --sm_msg表的id字段
AS
BEGIN
      declare @url varchar(4000)    --接口路由
      declare @object int                 --OLE对象实例
      declare @responseText varchar(4000) --文本
      set @url = 'http://localhost:8080/example/test01?id=' + @msgId
      
      print @url
      exec sp_OACreate'MSXML2.XMLHTTP',@object out
      exec sp_OAMethod @object,'open',null,'get',@url,'false'
      exec sp_OAMethod @object,'send'
      exec sp_OAMethod @object,'responseText',@responseText output
      print @responseText
      exec sp_OADestroy @object
      SET NOCOUNT ON;
END

四、编写java接口

@RequestMapping("/test01")
@ResponseBody
public String test01(String id){
    return id;
}