随笔 - 89 文章 - 0 评论 - 2 阅读 - 11万

创建触发器

需求：表A中插入/更新数据后，表B中也插入/更新当前数据。

插入

USE [库名称]
GO
/****** Object:  Trigger 触发器名称  Script Date: 2020/12/30 11:15:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger 触发器名称 on 表A for insert
as
SET XACT_ABORT ON
DECLARE @startTime datetime
DECLARE @endTime datetime
set @startTime = dateadd(ms,0,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))
set @endTime = dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,-1,getdate()), 0))
insert into 表B
select  [F_PayID]
      ,[F_DeviceID]
      ,[F_CompanyID]
      ,[F_PayCode]
      ,[F_BillCode]
      ,[F_ScanType]
      ,[F_PayType]
      ,[F_PayStatus]
      ,[F_PayAmount]
      ,[F_Purpose]
      ,[F_PaySource]
      ,[F_PayTime]
      ,[F_RegisterAmount]
from inserted
where F_PayID not in (select F_PayID from 表B)
and F_PayTime >=@startTime and F_PayTime <=@endTime
if @@error<>0
begin
    rollback
    raiserror('cnanot change',1,2) with seterror
    return
end

　　更新

USE 数据库名称
GO
/****** Object:  Trigger 触发器名称   Script Date: 2020/12/30 11:21:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER 触发器名称
ON 表A
AFTER UPDATE
AS
 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    -- Insert statements for trigger here
    declare 
               @F_PayID varchar(50), 
               @F_PayType int,
               @F_PayAmount decimal(18,2),
               @F_PayTime datetime,
           @Count int;
 
     select
      @F_PayID =F_PayID,
      @F_PayType = F_PayType,
      @F_PayAmount=F_PayAmount,
      @F_PayTime = F_PayTime
      from inserted;
<br>             --如果表B中存在当前数据，则更新表B中的数据，若没有则更新表C中的数据
             select @Count = count(F_PayID) from 表B where F_PayID=@F_PayID ;
             if @Count>0
                update 表B set F_PayType=@F_PayType ,F_PayTime = @F_PayTime ,F_PayAmount = @F_PayAmount
                where F_PayID=@F_PayID ;
             else
                update 表C set F_PayType=@F_PayType ,F_PayTime = @F_PayTime ,F_PayAmount = @F_PayAmount
                where F_PayID=@F_PayID ;
 
END