如何用触发器记录表字段的变化情况

最新推荐文章于 2022-10-31 18:08:59 发布

吉普赛的歌

最新推荐文章于 2022-10-31 18:08:59 发布

阅读量573

点赞数 1

分类专栏： SQL Server 文章标签： SQL触发器 table_change_log 字段变更数据库审计 afterupdate

本文链接：https://blog.csdn.net/yenange/article/details/121256943

版权

SQL Server 专栏收录该内容

292 篇文章 4 订阅

订阅专栏

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
 id INT IDENTITY(1,1) PRIMARY KEY,
 c1 NVARCHAR(10) NOT NULL,
 c2 INT 
)
GO
SET NOCOUNT ON
INSERT INTO t (c1,c2) VALUES('a',1)
INSERT INTO t (c1,c2) VALUES('b',2)
INSERT INTO t (c1,c2) VALUES('c',3)
GO
IF EXISTS(SELECT 1 FROM sys.tables AS t WHERE t.name='table_change_log')
DROP TABLE table_change_log
GO
CREATE TABLE [dbo].[table_change_log](
	[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[tableName] [varchar](100) NOT NULL,
	[pkId] [varchar](50) NOT NULL,
	[operateFlag] [char](1) NOT NULL,
	[operateTime] [datetime] NOT NULL,
	[changeLog] [nvarchar](max) NOT NULL,
	[operateUserId] [int] NOT NULL DEFAULT(0)
)
GO
-- =============================================
-- Author:  yenange
-- Create date: 2021-11-10
-- Description: 判断哪些字段发生过改变，并记入日志表
-- =============================================
CREATE TRIGGER dbo.trig_t_U 
   ON  dbo.t 
   AFTER UPDATE
AS 
BEGIN
 SET NOCOUNT ON;
 DECLARE @log NVARCHAR(MAX)
 INSERT INTO dbo.table_change_log
 (
 	-- id -- this column value is auto-generated
 	tableName,
 	pkId,
 	operateFlag,
 	operateTime,
 	changeLog,
 	operateUserId
 )
 SELECT 
	't' AS tableName
	,new.id AS pkId
	,'U' AS operateFlag
	,GETDATE() AS operateTime
	, CASE WHEN old.c1!=new.c1 THEN CONCAT('c1:',old.c1,'=>',new.c1,';') ELSE '' END
     +CASE WHEN old.c2!=new.c2 THEN CONCAT('c2:',old.c2,'=>',new.c2,';') ELSE '' END AS changeLog
	,0 AS operateUserId
	FROM INSERTED AS new INNER JOIN DELETED AS old
		ON  new.id=old.id
   ORDER BY new.id
END
GO
UPDATE t SET c1='aa',c2=c2+100 WHERE id IN(1,2)
UPDATE t SET c1='cc' WHERE id=3
GO
SELECT * FROM dbo.table_change_log AS tcl