首先开启 OLE Automation Procedures

参考文档：https://learn.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms191188(v=sql.105)?redirectedfrom=MSDN

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

创建触发器

CREATE TRIGGER [dbo].[触发器名称]
ON [dbo].[表名称]
WITH EXECUTE AS CALLER
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @data NVARCHAR(MAX);
    DECLARE @operation CHAR(1);
    DECLARE @result NVARCHAR(MAX);
    
    -- 判断操作类型
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        SET @operation = 'U'; -- 更新操作
    ELSE IF EXISTS (SELECT * FROM inserted)
        SET @operation = 'I'; -- 插入操作
    ELSE IF EXISTS (SELECT * FROM deleted)
        SET @operation = 'D'; -- 删除操作
    
    -- 根据操作类型获取相应数据
    IF @operation = 'I'
        SELECT @data = (SELECT * FROM inserted FOR JSON PATH);
    ELSE IF @operation = 'U'
        SELECT @data = (SELECT * FROM inserted FOR JSON PATH);
    ELSE IF @operation = 'D'
        SELECT @data = (SELECT * FROM deleted FOR JSON PATH);
    
    -- 构建JSON格式结果
    SET @result = '{"operation":"' + @operation + '","data":' + @data + '}';
    
    EXEC dbo.存储过程名称 @result;
END

创建存储过程

入参说明

• @data：存储过程的入参

变量说明

• @url：外部RESTful API接口
• @resp：接收sp_OACreate存储过程的返回值
• @obj：一个整数类型的变量，它作为 OLE 对象的引用

CREATE PROCEDURE [dbo].[存储过程名称] 
   @data NVARCHAR(MAX)
AS
BEGIN
	DECLARE @url NVARCHAR(200) = '这里替换成RESTful API接口地址'
    DECLARE @resp INT;
	DECLARE @obj INT;
    
    EXEC @resp = sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT;
    IF @resp = 0 BEGIN
        -- 发起POST请求 
        EXEC sp_OAMethod @obj, 'Open', NULL, 'POST', @url, 'false';
        EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/json';
        EXEC sp_OAMethod @obj, 'send', NULL, @data;
    END;
END

定义一个测试方法：

package com.jeesite.modules.test.web;

import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;

@Api(tags = "测试接收数据管理")
@RestController
@RequestMapping(value = "test/document")
public class TestWebController {

    @ApiOperation(value = "测试接收")
    @PostMapping(value = "test")
    @ResponseBody
    public void test(@RequestBody String objStr) {
        JSONObject jsonObject = JSONUtil.parseObj(objStr);
        System.out.println("test: \n" + jsonObject);
    }
}