SQL Server触发器调用java接口

叁歲伎倆 2023-02-09 14:22 1阅读 0赞

创建表

USE [test]
    GO
    /****** Object:  Table [dbo].[T_BD_SUPPLIER]    Script Date: 2020/5/19 15:03:17 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[T_BD_SUPPLIER](
    	[FSUPPLIERID] [int] NOT NULL,
    	[FAUDITDATE] [datetime] NULL,
     CONSTRAINT [PK_BD_SUPPLIER] PRIMARY KEY CLUSTERED 
    (
    	[FSUPPLIERID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[T_BD_SUPPLIER] ADD  CONSTRAINT [DF_T_BD_SUPPLIER_FSUPPLIERID]  DEFAULT ((0)) FOR [FSUPPLIERID]
    GO

添加数据

USE [test]
    GO
    INSERT INTO [dbo].[T_BD_SUPPLIER] ([FSUPPLIERID], [FAUDITDATE]) VALUES (N'1', N'2020-11-11 00:00:00.000')
    GO

添加触发器

USE [test]
    GO
    /****** Object:  Trigger [dbo].[trig_supplier_update]    Script Date: 2020/5/19 15:06:33 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Moses
    -- Create date: 2020/5/18 14:15:12
    -- Description:	Trigger [dbo].[trig_supplier_update]
    -- =============================================
    CREATE TRIGGER [dbo].[trig_supplier_update]
       ON  [dbo].[T_BD_SUPPLIER] 
       AFTER UPDATE
    AS 
    if UPDATE(FAUDITDATE)
    BEGIN
    	declare @FSUPPLIERID varchar(100);
    	select @FSUPPLIERID=[FSUPPLIERID] from t_bd_supplier;
    	exec PROC_SUPPLIER @FSUPPLIERID
    END

添加存储过程

USE [test]
    GO
    /****** Object:  StoredProcedure [dbo].[PROC_SUPPLIER]    Script Date: 2020/5/19 15:07:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[PROC_SUPPLIER]
    	@FSUPPLIERID varchar(100)		
    AS
    BEGIN
    	--接口路由
    	declare @url varchar(4000)	
    	--OLE对象实例
    	declare @object int			
    	--文本
    	declare @responseText varchar(4000) 
    	set @url = 'http://localhost:1111/api/kdIndexInfo?type=supplier&id='+@FSUPPLIERID
    	print @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

修改数据

USE [test]
    GO
    UPDATE [dbo].[T_BD_SUPPLIER]
       SET [FAUDITDATE] = '2020-11-11'
     WHERE [FSUPPLIERID] = 1
    GO

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200519152550609.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r,size_16,color_FFFFFF,t_70