创建Insert触发器
USE [appsmart]
GO
/****** Object: Trigger [dbo].[notify_trigger] Script Date: 01/25/2017 09:31:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------创建触发器
ALTER TRIGGER [dbo].[notify_trigger] ON [dbo].[Table_Test]
AFTER INSERT
AS
BEGIN
DECLARE @userName NVARCHAR(20) --Insert触发器 下面为inserted相当于触发器的一个虚拟插入表
set @userName=(select inserted.userName from inserted)
EXEC testProPush @userName ---调用存储过程并传参 如果传入的参数未变则不会触发
END
创建存储过程
USE [appsmart]
GO
/****** Object: StoredProcedure [dbo].[testPro] Script Date: 01/25/2017 09:18:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testPro](
@userName varchar(50)
)
AS
BEGIN
declare @ServiceUrl as varchar(1000)
PRINT 'http://192.168.0.124:8080/overhaul/iosPush.action?deviceToken='+@userName ---触发触发时传过来的参数
set @ServiceUrl='http://192.168.0.124:8080/overhaul/iosPush.action?deviceToken='+@userName
Declare @Object as Int
Declare @ResponseText as Varchar(8000)
Exec sp_OACreate'MSXML2.XMLHTTP',@Object OUT;
Exec sp_OAMethod @Object, 'open',NULL,'get',@ServiceUrl,'false'
Exec sp_OAMethod @Object,'send'
Exec sp_OAMethod @Object,'responseText',@ResponseText OUTPUT
Select @ResponseText
Exec sp_OADestroy @Object
END
在表中插入数据时触发器会调用接口
INSERT INTO [appsmart].[dbo].[Z_UserInfo] (userAccount, userName,userCode) VALUES
('123112', 'de1222','1');
1. 插入时必须插入userName字段
2. 若第二次插入时userName字段的值和上次一样则不会触发触发器