sqlServer触发器调用JavaWeb接口

sqlServer触发器调用JavaWeb接口

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

2、测试表

我们要实现的效果就是：当users表里插入一条数据后，触发器通过，发送http请求，请求java web接口，然后会往one表里插入一条数据。

users表

字段	类型
id	int
name	varchar

CREATE TABLE [dbo].[users] (
  [id] int  NOT NULL,
  [name] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  CONSTRAINT [PK__users__3213E83F7F60ED59] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
GO

ALTER TABLE [dbo].[users] SET (LOCK_ESCALATION = TABLE)
GO

one表

字段	类型
id	int
name	varchar

CREATE TABLE [dbo].[one] (
  [id] int  NOT NULL,
  [name] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  CONSTRAINT [PK__one__3213E83F03317E3D] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
GO

ALTER TABLE [dbo].[one] SET (LOCK_ESCALATION = TABLE)

3、触发器

触发器是建立在操作表上的，也就是users表上。

CREATE TRIGGER [dbo].[tr_users]
ON [dbo].[users]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
  -- Type the SQL Here.
	DECLARE @id int;
	declare @url varchar(4000);
	declare @object int;
	declare @responseText varchar(4000);
	
	SELECT @id = (select id from inserted);
	SELECT @url = 'http://192.168.31.133:8080/send/' + CONVERT(VARCHAR, @id);
	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'users表插入后触发器',
'SCHEMA', N'dbo',
'TABLE', N'users',
'TRIGGER', N'tr_users'