SQL server 触发器（监控表数据某字段更新后触发）

- 日理万妓 2022-03-27 02:12 682阅读 0赞

现有业务表 Business\_CustomerRisksBasicInfoStatus  
程序修改其中 \[HomeVisitReportCount\] \[LawyerLetterCount\] 两列时未记日志、  
由触发器实现监控目的、  
具体sql如下、

## 创建日志记录表 ##

CREATE TABLE [dbo].[tb_RisksStatusLog](
    	[Id] [UNIQUEIDENTIFIER] NOT NULL CONSTRAINT [DF_tb_RisksStatusLog_Id]  DEFAULT (NEWID()),
    	[Operate] [VARCHAR](50) NOT NULL,    ---操作
    	[UpdateColumn] [VARCHAR](50) NULL,  ---修改的列
    	[ApplicationNumber] [VARCHAR](50) NULL,  ---业务编号
    	[OldValue] [INT] NULL,   -----旧值
    	[NewValue] [INT] NULL,   -----新值
    	[OperatedAt] [DATETIME] NOT NULL CONSTRAINT [DF_tb_RisksStatusLog_OperatedAt]  DEFAULT   (GETDATE())     ---修改时间
    ) ON [PRIMARY]

--------------------

## 创建触发器 ##

CREATE TRIGGER [dbo].[tr_RisksBasicInfoStatus]
        ON [dbo].[Business_CustomerRisksBasicInfoStatus] AFTER UPDATE,INSERT,DELETE
        AS
        
    
        BEGIN 
       
          DECLARE @op VARCHAR(10)
       SELECT @op=CASE WHEN EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
                       THEN 'Update'
                       WHEN EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
                       THEN 'Insert'
                       WHEN NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
                       THEN 'Delete' END
    
    
           IF UPDATE(HomeVisitReportCount)  
           BEGIN
           INSERT INTO dbo.tb_RisksStatusLog
                   ( 
                     Operate ,
    				 UpdateColumn,
                     ApplicationNumber ,
                     OldValue ,
                     NewValue 
                   ) 
             SELECT @op,'HomeVisitReportCount',n.ApplicationNum,o.HomeVisitReportCount,n.HomeVisitReportCount  
             FROM inserted n
             LEFT JOIN deleted o ON o.id=n.id
           END
       IF UPDATE(LawyerLetterCount)   
           BEGIN
           INSERT INTO dbo.tb_RisksStatusLog
                   ( 
                     Operate ,
    				 UpdateColumn,
                     ApplicationNumber ,
                     OldValue ,
                     NewValue 
                   ) 
             SELECT @op,'LawyerLetterCount',n.ApplicationNum,o.LawyerLetterCount,n.LawyerLetterCount  
             FROM inserted n
             LEFT JOIN deleted o ON o.id=n.id
           END
        END

--------------------

# 搞定！ #