共计 3096 个字符,预计需要花费 8 分钟才能阅读完成。
这几天接到一个需要须要吧不同零碎的数据库进行同步,须要我做一个两头平台进行连贯,霎时就想到了触发器调用接口而后通过 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 执行插入另一个数据库。
而后执行插入语句就能够看到执行的后果了~
正文完