SQLServer添加UPDATE回滚日志(update/delete/insert)

上一篇文章我讲解了delete操作的回滚日志建立的方法，基于上一篇文章的基础，经过修改和增加，实现update操作的回滚日志的建立。首先要注意的是：这里的update并不只是表的update操作，它包含delete和insert操作，完全涵盖上一篇文章的内容。那上一篇文章岂不就多此一举，废话一堆啦？NO~NO~NO~前一篇文章是个启发，简单易懂。行了，废话就讲到这里了！

下面直接上代码（copy到你的数据库里面直接就可以运行）：

CREATE PROCEDURE [dbo].[SP_UPDATE_LOG]    @TABLENAME VARCHAR(50)ASBEGIN    SET NOCOUNT ON;    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = @TABLENAME AND TYPE = 'U' )    BEGIN        PRINT'ERROR：not exist table '+@TABLENAME        RETURN    END    IF (@TABLENAME LIKE'BACKUP_%' OR @TABLENAME='UPDATE_LOG' )    BEGIN        --PRINT'ERROR：not exist table '+@TABLENAME        RETURN    END    --================================判断是否存在 UPDATE_LOG 表============================    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = 'UPDATE_LOG' AND TYPE = 'U')        CREATE TABLE UPDATE_LOG        (            UpdateGUID VARCHAR(36),            UpdateTime DATETIME,            TableName varchar(20),            UpdateType varchar(6),            RollBackSQL varchar(MAX),            ExecSQL VARCHAR(500)        )    --=================================判断是否存在 BACKUP_ 表================================    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = 'BACKUP_'+@TABLENAME AND TYPE = 'U')    BEGIN        DECLARE test_Cursor CURSOR FOR        SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns        WHERE TABLE_NAME=@TABLENAME        OPEN test_Cursor        DECLARE @SQLTB NVARCHAR(MAX)=''        DECLARE @COLUMN_NAME NVARCHAR(50),@DATA_TYPE VARCHAR(20),@CHARACTER_MAXIMUM_LENGTH INT        FETCH NEXT FROM test_Cursor INTO @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH        WHILE @@FETCH_STATUS=0        BEGIN            SET @SQLTB=@SQLTB+'['+@COLUMN_NAME+'] '+@DATA_TYPE+CASE ISNULL(@CHARACTER_MAXIMUM_LENGTH,0) WHEN 0 THEN '' WHEN -1 THEN '(MAX)' ELSE'('+CAST(@CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+')' END+','            FETCH NEXT FROM test_Cursor INTO @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH        END        SET @SQLTB='CREATE TABLE BACKUP_'+@TABLENAME+' (UpdateGUID varchar(36),UpdateType Varchar(10),'+SUBSTRING(@SQLTB,1,LEN(@SQLTB)-1)+')'        EXEC (@SQLTB)        CLOSE test_Cursor        DEALLOCATE test_Cursor    END    --======================================判断是否存在 UPDATE 触发器=========================    IF NOT EXISTS(SELECT * FROM sys.objects WHERE NAME = 'tg_'+@TABLENAME+'_Update' AND TYPE = 'TR')    BEGIN        DECLARE @SQLTR NVARCHAR(MAX)        SET @SQLTR='CREATE TRIGGER tg_'+@TABLENAME+'_Update    ON  '+@TABLENAME+'    AFTER Update,Delete,InsertASBEGIN     SET NOCOUNT ON;    --==============================获取GUID==========================================    DECLARE @NEWID VARCHAR(36)=NEWID()     --===========================将删掉或新增的数据插入备份表=========================    DECLARE @ROWCOUNT INT    INSERT INTO [dbo].[BACKUP_'+@TABLENAME+']    SELECT @NEWID,''DELETE'',* FROM deleted    SET @ROWCOUNT=@@ROWCOUNT    IF @ROWCOUNT>0    BEGIN        INSERT INTO [dbo].[BACKUP_'+@TABLENAME+']        SELECT @NEWID,''INSERT'',* FROM inserted    END    ELSE    BEGIN        INSERT INTO [dbo].[BACKUP_'+@TABLENAME+']        SELECT @NEWID,''INSERT'',* FROM inserted        SET @ROWCOUNT=@@ROWCOUNT    END     --==============================记录日志和回滚操作的SQL===========================                   --******************生成插入语句用到的列名（需避开自增字段）********************    DECLARE @COLUMN1 NVARCHAR(MAX)=''''    SELECT @COLUMN1+='',[''+COLUMN_NAME+'']'' FROM INFORMATION_SCHEMA.columns    WHERE TABLE_NAME='''+@TABLENAME+'''    AND COLUMNPROPERTY(OBJECT_ID('''+@TABLENAME+'''),COLUMN_NAME,''IsIdentity'')<>1 --非自增字段    SET @COLUMN1=SUBSTRING(@COLUMN1,2,LEN(@COLUMN1))                                               --*******************动态定义变量、删除条件匹配的列********************    DECLARE @DECLARE VARCHAR(MAX)='''',@INTODECLARE VARCHAR(MAX)='''',@WHERE VARCHAR(MAX)='''',@COLUMN2 VARCHAR(MAX)=''''    SELECT @DECLARE+=''@''+COLUMN_NAME+'' ''+DATA_TYPE+CASE ISNULL(CAST(CHARACTER_OCTET_LENGTH AS VARCHAR(10)),'''') WHEN '''' THEN '','' WHEN ''-1'' THEN ''(MAX),'' ELSE ''(''+CAST(CHARACTER_OCTET_LENGTH AS VARCHAR(10))+''),'' END,        @INTODECLARE+=''@''+COLUMN_NAME+'','',        @COLUMN2+=''[''+COLUMN_NAME+''],'' ,        @WHERE += ''ISNULL(''+ COLUMN_NAME+'','''''''')=ISNULL(@''+COLUMN_NAME+'','''''''') AND ''    FROM INFORMATION_SCHEMA.columns    WHERE TABLE_NAME='''+@TABLENAME+'''    SET @DECLARE=LEFT(@DECLARE,LEN(@DECLARE)-1)    SET @INTODECLARE=LEFT(@INTODECLARE,LEN(@INTODECLARE)-1)    SET @COLUMN2=LEFT(@COLUMN2,LEN(@COLUMN2)-1)    SET @WHERE= LEFT(@WHERE,LEN(@WHERE)-3)       --*******************判断是否还原当前表的最近一次操作*******************             DECLARE @SQL_ISLAST VARCHAR(MAX)=''    SET NOCOUNT ON    DECLARE @maxdate datetime    SELECT @maxdate=max(updatetime) FROM UPDATE_LOG WHERE TableName='''''+@TABLENAME+'''''    IF NOT EXISTS(SELECT 1 FROM UPDATE_LOG WHERE UpdateTime=@maxdate AND UPDATEGUID=''''''+@NEWID+'''''')    BEGIN        DECLARE @MAXGUID VARCHAR(50)        SELECT @MAXGUID=UPDATEGUID FROM UPDATE_LOG WHERE UpdateTime=@maxdate        PRINT ''''此操作并非最近一次操作，请逐步还原,此表最近一次操作的GUID是：''''+@MAXGUID        RETURN    END    ''     --********************还原insert和update操作用到的SQL*******************     DECLARE @SQL_DELETE VARCHAR(MAX)=''    SET ROWCOUNT 1  --设定相同条件下只删除1行            DECLARE Cursor_ CURSOR FOR    SELECT ''+@COLUMN2+'' FROM BACKUP_'+@TABLENAME+' WHERE UPDATEGUID= ''''''+@NEWID+'''''' AND UpdateType=''''INSERT''''    OPEN Cursor_    DECLARE ''+@DECLARE+''    FETCH NEXT FROM Cursor_ INTO ''+@INTODECLARE+''    WHILE @@FETCH_STATUS=0    BEGIN                          DELETE FROM '+@TABLENAME+' WHERE ''+@WHERE+''        FETCH NEXT FROM Cursor_ INTO ''+@INTODECLARE+''    END    CLOSE Cursor_    DEALLOCATE Cursor_    SET ROWCOUNT 0    ''     --*********************还原delete和update操作用到的SQL*******************     DECLARE @SQL_INSERT VARCHAR(MAX)=''    INSERT INTO '+@TABLENAME+' SELECT ''+@COLUMN1+'' FROM BACKUP_'+@TABLENAME+' WHERE UPDATEGUID=''''''+@NEWID+'''''' AND UpdateType=''''DELETE''''    ''     --*********************还原操作之后把备份表和log表的记录删掉*************     DECLARE @SQL_DELGUID VARCHAR(MAX)=''    DELETE FROM BACKUP_'+@TABLENAME+' WHERE  UPDATEGUID IN(SELECT UPDATEGUID FROM UPDATE_LOG WHERE UpdateTime>=@maxdate AND TableName='''''+@TABLENAME+''''')    DELETE FROM UPDATE_LOG WHERE UpdateTime>=@maxdate AND TableName='''''+@TABLENAME+'''''    PRINT ''''回滚操作执行成功，共恢复 ''+CAST(@ROWCOUNT AS VARCHAR(10))+'' 条记录''''    SET NOCOUNT OFF    ''     --*********************执行还原操作的SQL**********************************     DECLARE @EXECSQL VARCHAR(500)=''    DECLARE @SQL VARCHAR(MAX)    SELECT @SQL=ROLLBACKSQL FROM UPDATE_LOG WHERE UPDATEGUID=''''''+@NEWID+''''''      EXEC(@SQL)     ''     --==============================判断执行的哪种操作方式=================================     DECLARE @DoType VARCHAR(MAX)=''UPDATE''    IF NOT EXISTS(SELECT 1 FROM deleted)        SET @DoType=''INSERT''    IF NOT EXISTS(SELECT 1 FROM inserted)        SET @DoType=''DELETE''    IF NOT EXISTS(SELECT 1 FROM deleted) AND  NOT EXISTS(SELECT 1 FROM inserted)        RETURN    IF @DoType=''UPDATE''    BEGIN        INSERT INTO [dbo].[UPDATE_LOG]        SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''UPDATE'',@SQL_ISLAST+@SQL_DELETE+@SQL_INSERT+@SQL_DELGUID,@EXECSQL        RETURN    END    IF @DoType=''DELETE''    BEGIN        INSERT INTO [dbo].[UPDATE_LOG]        SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''DELETE'',@SQL_ISLAST+@SQL_INSERT+@SQL_DELGUID,@EXECSQL        RETURN    END    IF @DoType=''INSERT''    BEGIN        INSERT INTO [dbo].[UPDATE_LOG]        SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''INSERT'',@SQL_ISLAST+@SQL_DELETE+@SQL_DELGUID,@EXECSQL        RETURN    ENDEND            '        EXEC (@SQLTR)    ENDEND 

运行这段代码，你会创建一个存储过程，下面来建一个测试表简单测一下这个存储过程的功能吧：

CREATE TABLE test([id] [int] NULL,[name] [varchar](10) NULL) INSERT INTO testSELECT 1,'a'UNION ALLSELECT 2,'b'UNION ALLSELECT 3,'c'UNION ALLSELECT 4,'d'UNION ALLSELECT 5,'a'UNION ALLSELECT 6,'b'SELECT * FROM test

检查一下，表建好了：

接着执行存储过程给test表添加回滚日志：

EXEC SP_UPDATE_LOG 'test'--给test表建立update回滚日志SELECT * FROM [dbo].[BACKUP_test]--test表数据备份SELECT * FROM [dbo].[UPDATE_LOG]--update操作记录

这时候你会发现生成了两张表:backup_test 和 update_log，包括test表下建立了触发器，backup_test是test表的备份表，由test表专用，update_log表是所有建立update回滚日志的表所公用的。这个表里面记录每张表操作的时间，做了何种操作，包括执行回滚的SQL：

下面测一下回滚的功能吧，我要把test表改得面目全非，然后再执行回滚：

update 操作被我一不小心执行了两次，变成了这副德行，下面我开始还原操作，先查询下update_log这张表：

产生了两条操作记录，copy ExecSQL里面的SQL语句执行，注意要先执行时间最近的操作记录，一步一步还原：

还原一步之后变成了这个样子，下面再还原一步：

到目前为止看下完全还原了吧~

好了，这个update的回滚日志也该介绍完了~终于打完了，手好累啊~~

PS:执行delete和insert操作也是一样的回滚步骤