@ 数据库触发器请求webapi模板

最新推荐文章于 2024-09-14 19:55:29 发布

Devout-mzc

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量342

点赞数 10

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_42623766/article/details/136329988

版权


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sasomain_update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[sasomain_update]
GO

create trigger sasomain_update
on [dbo].SO_SOMain 
after update
as
begin
   IF(UPDATE(iStatus))
   BEGIN
    declare @Code nvarchar(50)='',@iStatus int,@ErrorMsg nvarchar(max);
    DECLARE @url VARCHAR(256); -- 定义变量保存外部接口URL地址
	DECLARE @Object Int;
	DECLARE @Data NVARCHAR(MAX);
   DECLARE @Method NVARCHAR(10) = 'POST';
	DECLARE @response NVARCHAR(MAX);
    DECLARE @ResponseText   varchar(8000) =''  
	DECLARE @hr   varchar(800) ='' 
	
    --SET @url = 'xxxxxx'; -- 设置外部接口URL地址

	select @Code = cSOCode,@iStatus=iStatus from inserted

	if(@iStatus=1)
	begin
			IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'E' AND type = 'U')
			   PRINT 'Table does not exist'
			ELSE
			  PRINT 'Table exists'
			set @data= isnull('{"code":"'+@Code+'","ordertype":1}','');
			设置HTTP请求参数
			DECLARE @Headers NVARCHAR(4000) = '';
			DECLARE @Body NVARCHAR(MAX) = @Data;
			Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
			Exec sp_OAMethod @Object, 'open', NULL, 'POST',@url,'false'
			EXEC SP_OAMethod @object,'setRequestHeader',NULL,'Connection','Keep-Alive';
			EXEC SP_OAMethod @object,'setRequestHeader',NULL,'Keep-Alive','timeout=60000';
			EXEC sp_OAMethod @object,'setRequestHeader',NULL,'Content-Type','application/json'
			Exec sp_OAMethod @Object, 'send', NULL, @Data --发送数据   发送请求头
			EXEC @hr = sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
			Exec sp_OADestroy @Object
		  --处理HTTP响应结果（可根据需要自定义）
			PRINT @ResponseText;
	 END 
   END
end