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

来源：互联网发布：知数科技编辑：程序博客网时间：2024/05/22 20:03

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 databaseFOR DDL_TABLE_EVENTSASDECLARE @data XMLDECLARE @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

CREATE TABLE [dbo].[test]([id] [int] NULL,[name] [varchar](20) NULL,[age] [int] NULL)

触发器

create trigger [dbo].[tr_test] on [dbo].[test]with execute as caller after insert,delete,updateasbegin     declare @NCT int     set @NCT = @@OPTIONS & 512     set nocount on         IF EXISTS (SELECT 1 FROM INSERTED ) AND NOT EXISTS (SELECT 1 FROM DELETED)  -- 插入          begin                     insert into history(operation,tablename,val)                  select 'insert','test','{'+                  case when inserted.id is null then '"id":null' else ('"id":"'+replace(replace(convert(varchar(40),inserted.id),'\','\\'),'"','\"')+'"') end + ',' +                   case when inserted.name is null then '"name":null' else ('"name":"'+replace(replace(replace(inserted.name,'\','\\'),'"','\"'),CHAR(10),'\n')+'"') end + ',' +                   case when inserted.age is null then '"age":null' else ('"age":"'+replace(replace(convert(varchar(40),inserted.age),'\','\\'),'"','\"')+'"') end + '}'                  from inserted         end              ELSE  IF EXISTS (SELECT 1 FROM INSERTED ) AND EXISTS (SELECT 1 FROM DELETED)  -- 更新          begin                     insert into history(operation,tablename,val)                  select 'delete','test','{'+                  case when deleted.id is null then '"id":null' else ('"id":"'+replace(replace(convert(varchar(40),deleted.id),'\','\\'),'"','\"')+'"') end + ',' +                                     case when deleted.name is null then '"name":null' else ('"name":"'+replace(replace(replace(deleted.name,'\','\\'),'"','\"'),CHAR(10),'\n')+'"') end + ',' +                                     case when deleted.age is null then '"age":null' else ('"age":"'+replace(replace(convert(varchar(40),deleted.age),'\','\\'),'"','\"')+'"') end + '}'                                    from deleted                   insert into history(operation,tablename,val)                  select 'insert','test','{'+                  case when inserted.id is null then '"id":null' else ('"id":"'+replace(replace(convert(varchar(40),inserted.id),'\','\\'),'"','\"')+'"') end + ',' +                  case when inserted.name is null then '"name":null' else ('"name":"'+replace(replace(replace(inserted.name,'\','\\'),'"','\"'),CHAR(10),'\n')+'"') end + ',' +                  case when inserted.age is null then '"age":null' else ('"age":"'+replace(replace(convert(varchar(40),inserted.age),'\','\\'),'"','\"')+'"') end + '}'                  from inserted         end         ELSE  IF NOT EXISTS (SELECT 1 FROM INSERTED ) AND EXISTS (SELECT 1 FROM DELETED)  -- 删除         begin                  insert into history(operation,tablename,val)                  select 'delete','test','{'+                  case when deleted.id is null then '"id":null' else ('"id":"'+replace(replace(convert(varchar(40),deleted.id),'\','\\'),'"','\"')+'"') end + ',' +                  case when deleted.name is null then '"name":null' else ('"name":"'+replace(replace(replace(deleted.name,'\','\\'),'"','\"'),CHAR(10),'\n')+'"') end + ',' +                  case when deleted.age is null then '"age":null' else ('"age":"'+replace(replace(convert(varchar(40),deleted.age),'\','\\'),'"','\"')+'"') end + '}'                  from deleted         end        if (@NCT = 0) set nocount offend

测试DDL：

create table mytest (col1 int, col2 varchar(100), col3 varchar(200))

history表捕获到的数据情况

12017-04-27 11:49:35.100ddlNULLcreate table mytest (col1 int, col2 varchar(100), col3 varchar(200))      NULLNULL

测试DML：

insert into mytest values(1,'Tom','Jim'),(2,'Tom','Jim'),(3,'Tom','Jim')id         opertime          operation tablename      val                         reserve1  reserve222017-04-27 11:52:21.383insertmytest{"col1":"3","col2":"Tom","col3":"Jim"}NULLNULL32017-04-27 11:52:21.383insertmytest{"col1":"2","col2":"Tom","col3":"Jim"}NULLNULL42017-04-27 11:52:21.383insertmytest{"col1":"1","col2":"Tom","col3":"Jim"}NULLNULL

0 0