SQLServer触发器调用JavaWeb接口的过程详解

这几天接到一个需求需要吧不同系统的数据库进行同步，需要我做一个中间平台进行连接，瞬间就想到了触发器调用接口然后通过api进行传递再写入另一个数据库。
sqlserver触发器调用javaweb接口
1、开启 ole automation procedures
sqlserver要想调用web接口，就要使用自带的存储过程。而这些存储过程2005版本以后默认时关闭的，所以要先开启。

 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

关闭 ole automation procedures

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

关闭高级选项

sp_configure 'show advanced options', 0;
go
reconfigure;
go

然后就可以进行写触发器，因为我这边表的内容比较多，大家直接看代码就好了。

create trigger [dbo].[kingdee]
on [dbo].[称重信息]
with execute as caller
for insert
as
begin
 -- type the sql here.
	declare @fbillno int;
	declare @carid varchar(4000);
	declare @type varchar(4000);
	declare @sendplace varchar(4000);	
	declare @consignee varchar(4000);		
	declare @material varchar(4000);
	declare @specification varchar(4000);
	declare @gw varchar(4000);
	declare @tare varchar(4000);	
	declare @nw varchar(4000);
	declare @kw varchar(4000);
  declare @aw varchar(4000);
	declare @price varchar(4000);
	declare @money varchar(4000);
	declare @zfxs varchar(4000);
	declare @square varchar(4000);
	declare @weighingcost varchar(4000);
	declare @gwtime varchar(4000);
	declare @taretime varchar(4000);
	declare @url varchar(4000);
	declare @object int;
	declare @responsetext varchar(4000);
	
	select @fbillno = (select 流水号 from inserted);
	select @carid = (select 车号 from inserted);
	select @type = (select 过磅类型 from inserted);
	select @sendplace = (select 发货单位 from inserted);
	select @consignee = (select 收货单位 from inserted);
	select @material = (select 货名 from inserted);
	select @specification = (select 规格 from inserted);	
	select @gw = (select 毛重 from inserted);
	select @tare = (select 皮重 from inserted);
	select @nw = (select 净重 from inserted);	
	select @kw = (select 扣重 from inserted);
	select @aw = (select 实重 from inserted);
	select @price = (select 单价 from inserted);	
	select @money = (select 金额 from inserted);
	select @zfxs = (select 折方系数 from inserted);
	select @square = (select 方量 from inserted);	
	select @weighingcost = (select 过磅费 from inserted);
	select @gwtime = (select 毛重时间 from inserted);
	select @taretime = (select 皮重时间 from inserted);
	select @url = 'http://ip/saveweight/' + convert(int, @fbillno)+'/'+ convert(varchar, @carid)
	+'/'+ convert(varchar, @type)+'/'+ convert(varchar, @sendplace)+'/'+ convert(varchar, @consignee)+'/'+ convert(varchar, @material)
	+'/'+ convert(varchar, @specification)+'/'+ convert(varchar, @gw)+'/'+ convert(varchar, @tare)+'/'+ convert(varchar, @nw)
	+'/'+ convert(varchar, @kw)+'/'+ convert(varchar, @aw)+'/'+ convert(varchar, @price)+'/'+ convert(varchar, @money)
	+'/'+ convert(varchar, @zfxs)+'/'+ convert(varchar, @square)+'/'+ convert(varchar, @weighingcost)+'/'+ convert(varchar, @gwtime)
	+'/'+ convert(varchar, @taretime);
	print @url;
-- 	insert into one (id, name) values (@id, @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
go

exec sp_addextendedproperty
'ms_description', n'称重信息表插入后触发器',
'schema', n'dbo',
'table', n'称重信息',
'trigger', n'kingdee'

看着复杂其实一点也不复杂，因为这个数据库的表是中文（不是我建的表系统中的中文的我没办法改），大致就是获取插入的数据然后进行触发，请求，然后api执行插入另一个数据库。
然后执行插入语句就可以看到执行的结果了~

到此这篇关于sqlserver触发器调用javaweb接口的过程详解的文章就介绍到这了,更多相关sqlserver触发器调用接口内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

SQLServer触发器调用JavaWeb接口的过程详解

相关推荐