SQL监控数据修改源

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

源码如下：

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