SQL监控数据修改源_mssql监控sql字段数据修改-CSDN博客

本文链接：https://blog.csdn.net/lsgis2001/article/details/130765157

该SQL脚本创建了一个名为UPDATE_IN_BILL_RECORD_EXTEND的触发器，用于在ZY数据库的IN_BILL_RECORD_EXTEND表发生更新后，记录修改数据的过程或方法。触发器通过DBCCINPUTBUFFER获取SPID信息，并将相关信息存储到REPORT数据库的IN_BILL_RECORD_EXTEND_DELETE表中，以便追踪和审计数据变更。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

利用触发器，监控数据是被哪个过程或方法修改的。

源码如下：

USE [ZY]
GO

/****** Object:  Trigger [UPDATE_IN_BILL_RECORD_EXTEND]    Script Date: 05/19/2023 12:24:08 ******/
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[IN].[UPDATE_IN_BILL_RECORD_EXTEND]'))
DROP TRIGGER [IN].[UPDATE_IN_BILL_RECORD_EXTEND]
GO

USE [ZY]
GO

/****** Object:  Trigger [IN].[UPDATE_IN_BILL_RECORD_EXTEND]    Script Date: 05/19/2023 12:24:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [IN].[UPDATE_IN_BILL_RECORD_EXTEND] ON [ZY].[IN].[IN_BILL_RECORD_EXTEND]
    AFTER UPDATE
AS
    BEGIN
        DECLARE @SPID VARCHAR(50)
        SET @SPID = CAST(@@SPID AS VARCHAR(50))
        
        CREATE TABLE #T
            (
              EVENTTYPE VARCHAR(20) ,
              PARAMETERS INT ,
              EVENTINFO VARCHAR(5000)
            )  
            
        INSERT  #T
                EXEC ( 'DBCC INPUTBUFFER (' + @SPID + ')'
                    )
        --提前建立这个表结构；
        INSERT  INTO [REPORT].[dbo].[IN_BILL_RECORD_EXTEND_DELETE] ( 
                                                              [IN_BILL_ID],
                                                              
                                                              [CREATE_TIME],
                                                              [REMARK],
                                                              OP_TYPE )
                SELECT   [IN_BILL_ID],  GETDATE(),
                         ( SELECT TOP 1
                                            EVENTINFO
                                    FROM    #T
                                  ),'UPDATE'
                FROM    DELETED

        DROP TABLE #T

    END





GO

USE [REPORT]
GO

/****** Object:  Table [dbo].[IN_BILL_RECORD_EXTEND_DELETE]    Script Date: 05/19/2023 13:49:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[IN_BILL_RECORD_EXTEND_DELETE](
	[AUTO_ID] [DECIMAL](18, 0) IDENTITY(1,1) NOT NULL,
	[IN_BILL_ID] [DECIMAL](18, 0) NULL,
	[CREATE_TIME] [DATETIME] NULL,
	[REMARK] [VARCHAR](8000) NULL,
	[OP_TYPE] [VARCHAR](50) NULL,
 CONSTRAINT [PK_IN_BILL_RECORD_EXTEND_DELETE] PRIMARY KEY CLUSTERED 
(
	[AUTO_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO