SQLServer触发器的使用

创建一个触发器

CREATE TRIGGER decor--触发器名称
   ON dbo.SG_DecorationEntry --表名
   AFTER INSERT --操作(增删改)
AS 
BEGIN
    DECLARE @id UNIQUEIDENTIFIER 
    SELECT @id=Inserted.DecorationEntryID FROM Inserted --添加操作时的数据
    --需要执行的业务
    INSERT INTO dbo.SG_DecorationEntryAPLog
            ( DecorationEntryAPLogID ,
              DecorationEntryID ,
              ApprovalAction ,
              ApprovalUser ,
              ApprovalDate ,
              ApprovalOpinion ,
              ApprovalUserCode
            )
    VALUES  ( NEWID() , -- DecorationEntryAPLogID - uniqueidentifier
              @id , -- DecorationEntryID - uniqueidentifier
              N'' , -- ApprovalAction - nvarchar(50)
              N'' , -- ApprovalUser - nvarchar(50)
              GETDATE() , -- ApprovalDate - datetime
              N'' , -- ApprovalOpinion - nvarchar(1000)
              N''  -- ApprovalUserCode - nvarchar(50)
            )


END

也可以在触发器中调用接口

CREATE TRIGGER decor
ON dbo.SG_DecorationEntry 
AFTER INSERT 
AS 
BEGIN

declare @ServiceUrl as varchar(1000) 
declare @UrlAddress varchar(500)
--WebService地址:以http开头,结尾带斜杠,例如'http://webservice.webxml.com.cn/WebServices/MobileCodeWS.asmx/' 
set @UrlAddress = '....'
declare @FunName varchar(50)
--WebService中调用的方法名:例如'getMobileCodeInfo'
set @FunName = 'GetEntryAndExitData' 
--以下参数对应WebService中4个参数的[参数名]
declare @P1 varchar(100),@P2 varchar(100)
set @P1 = 'data'
set @P2 = 'userid'
declare @P1_Value varchar(max),@P2_Value varchar(800)
set @P1_Value = '{"OrganizationID": "BDA02110-39BF-48CE-8D00-E4D31A45EE88"}'
set @P2_Value = ''
set @ServiceUrl = @UrlAddress + @FunName + '?' + @P1 + '=' + @P1_Value 
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
posted @ 2019-09-12 17:14  何以平天下  阅读(1173)  评论(0编辑  收藏  举报