MS SQLSERVER实时增量DML和DDL数据捕获

最新推荐文章于 2024-08-22 07:00:00 发布

JsLyn

最新推荐文章于 2024-08-22 07:00:00 发布

阅读量1.9k

点赞数

分类专栏： SQL Server

本文链接：https://blog.csdn.net/u012417806/article/details/70846668

版权

本文介绍了如何使用SQLSERVER的触发器来实现实时增量DML和DDL数据捕获。通过创建数据库触发器tr_ddl，结合inserted和deleted临时表，可以有效地跟踪数据库中的数据变化。此外，作者提到了dbcc inputbuffer虽然能捕捉操作语句，但在处理动态批量插入时存在局限性。最后，展示了测试过程和捕获到的数据格式，数据以JSON格式存储在汇总表中。

摘要由CSDN通过智能技术生成

SQLSERVER提供了很好的触发器捕获数据方法，针对DML数据库可以从inserted和deleted临时表获取数据，当然也可使用dbcc inputbuffer，但是个人不推荐这种方法，dbcc inputbuffer能捕捉原始操作语句，但是对于动态批量插入数据时就很傻瓜了，只能捕捉到第一条操作语句。

首先建一张表，用来存储数据（所有表的数据汇总到一张表里面），方便导出数据

CREATE TABLE [dbo].[history](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[opertime] [datetime] NOT NULL,
	[operation] [varchar](10) NULL,
	[tablename] [varchar](50) NULL,
	[val] [varchar](max) NULL,
	[reserve1] [varchar](200) NULL,
	[reserve2] [varchar](200) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[history] ADD  DEFAULT (getdate()) FOR [opertime]

1.DDL数据捕获

利用Sqlserver提供的数据级触发器事件DDL_TABLE_EVENTS，创建出数据库触发器tr_ddl。

CREATE TRIGGER [tr_ddl] ON database
FOR DDL_TABLE_EVENTS
AS
DECLARE @data XML
DECLARE @cmd VARCHAR(max)
SET @data=EVENTDATA()
SET @cmd=@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(max)')
SET @cmd=LTRIM(RTRIM(REPLACE(@cmd,'','')))
INSERT INTO history(operation,val) VALUES('ddl',@cmd)

2.DML数据捕获

从inserted和deleted获取数据，保存到汇总表里面，我将数据做了一定的格式处理，最终保存到表里的数据是JSON格式

测试表test