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'
4、web接口
- pom.xml
- application.properties
- web接口
5、结果
users表中插入 （123，'123'）数据：
通过web接口，one表中已经插入了（123，'name123'）数据：
接口调用日志：
