SQLServer 自定义回滚表(update/delete/insert)并实行回滚

实现update操作的回滚日志的建立。首先要注意的是：这里的update并不只是表的update操作，它包含delete和insert操作！

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

[sql] view plain copy print ?

1. CREATE PROCEDURE [dbo].[SP_UPDATE_LOG]  
2.     @TABLENAME VARCHAR(50)  
3. AS  
4. BEGIN  
5.     SET NOCOUNT ON;  
6.     IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = @TABLENAME AND TYPE = ‘U’ )  
7.     BEGIN  
8.         PRINT’ERROR：not exist table ’+@TABLENAME  
9.         RETURN  
10.     END  
11.     IF (@TABLENAME LIKE‘BACKUP_%’ OR @TABLENAME=‘UPDATE_LOG’ )  
12.     BEGIN  
13.         –PRINT’ERROR：not exist table ’+@TABLENAME  
14.         RETURN  
15.     END  
16.     –================================判断是否存在 UPDATE_LOG 表============================  
17.     IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = ‘UPDATE_LOG’ AND TYPE = ‘U’)  
18.         CREATE TABLE UPDATE_LOG  
19.         (  
20.             UpdateGUID VARCHAR(36),  
21.             UpdateTime DATETIME,  
22.             TableName varchar(20),  
23.             UpdateType varchar(6),  
24.             RollBackSQL varchar(MAX),  
25.             ExecSQL VARCHAR(500)  
26.         )  
27.     –=================================判断是否存在 BACKUP_ 表================================  
28.     IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = ‘BACKUP_’+@TABLENAME AND TYPE = ‘U’)  
29.     BEGIN  
30.         DECLARE test_Cursor CURSOR FOR  
31.         SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns  
32.         WHERE TABLE_NAME=@TABLENAME  
33.         OPEN test_Cursor  
34.         DECLARE @SQLTB NVARCHAR(MAX)=”  
35.         DECLARE @COLUMN_NAME NVARCHAR(50),@DATA_TYPE VARCHAR(20),@CHARACTER_MAXIMUM_LENGTH INT  
36.         FETCH NEXT FROM test_Cursor INTO @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH  
37.         WHILE @@FETCH_STATUS=0  
38.         BEGIN  
39.             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+‘,’  
40.             FETCH NEXT FROM test_Cursor INTO @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH  
41.         END  
42.         SET @SQLTB=‘CREATE TABLE BACKUP_’+@TABLENAME+‘ (UpdateGUID varchar(36),UpdateType Varchar(10),’+SUBSTRING(@SQLTB,1,LEN(@SQLTB)-1)+‘)’  
43.         EXEC (@SQLTB)  
44.         CLOSE test_Cursor  
45.         DEALLOCATE test_Cursor  
46.     END  
47.     –======================================判断是否存在 UPDATE 触发器=========================  
48.     IF NOT EXISTS(SELECT * FROM sys.objects WHERE NAME = ‘tg_’+@TABLENAME+‘_Update’ AND TYPE = ‘TR’)  
49.     BEGIN  
50.         DECLARE @SQLTR NVARCHAR(MAX)  
51.         SET @SQLTR=’  
52. CREATE TRIGGER tg_‘+@TABLENAME+’_Update  
53.     ON  ‘+@TABLENAME+’  
54.     AFTER Update,Delete,Insert  
55. AS  
56. BEGIN   
57.     SET NOCOUNT ON;  
58.     –==============================获取GUID==========================================  
59.     DECLARE @NEWID VARCHAR(36)=NEWID()  
60.    
61.     –===========================将删掉或新增的数据插入备份表=========================  
62.     DECLARE @ROWCOUNT INT  
63.     INSERT INTO [dbo].[BACKUP_‘+@TABLENAME+’]  
64.     SELECT @NEWID,”DELETE”,* FROM deleted  
65.     SET @ROWCOUNT=@@ROWCOUNT  
66.     IF @ROWCOUNT>0  
67.     BEGIN  
68.         INSERT INTO [dbo].[BACKUP_‘+@TABLENAME+’]  
69.         SELECT @NEWID,”INSERT”,* FROM inserted  
70.     END  
71.     ELSE  
72.     BEGIN  
73.         INSERT INTO [dbo].[BACKUP_‘+@TABLENAME+’]  
74.         SELECT @NEWID,”INSERT”,* FROM inserted  
75.         SET @ROWCOUNT=@@ROWCOUNT  
76.     END  
77.    
78.     –==============================记录日志和回滚操作的SQL===========================  
79.                 
80.    
81.     –******************生成插入语句用到的列名（需避开自增字段）********************  
82.     DECLARE @COLUMN1 NVARCHAR(MAX)=””  
83.     SELECT @COLUMN1+=”,[”+COLUMN_NAME+”]” FROM INFORMATION_SCHEMA.columns  
84.     WHERE TABLE_NAME=”‘+@TABLENAME+’”  
85.     AND COLUMNPROPERTY(OBJECT_ID(”‘+@TABLENAME+’”),COLUMN_NAME,”IsIdentity”)<>1 –非自增字段  
86.     SET @COLUMN1=SUBSTRING(@COLUMN1,2,LEN(@COLUMN1))  
87.    
88.                  
89.                              
90.     –*******************动态定义变量、删除条件匹配的列********************  
91.     DECLARE @DECLARE VARCHAR(MAX)=””,@INTODECLARE VARCHAR(MAX)=””,@WHERE VARCHAR(MAX)=””,@COLUMN2 VARCHAR(MAX)=””  
92.     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,  
93.         @INTODECLARE+=”@”+COLUMN_NAME+”,”,  
94.         @COLUMN2+=”[”+COLUMN_NAME+”],” ,  
95.         @WHERE += ”ISNULL(”+ COLUMN_NAME+”,””””)=ISNULL(@”+COLUMN_NAME+”,””””) AND ”  
96.     FROM INFORMATION_SCHEMA.columns  
97.     WHERE TABLE_NAME=”‘+@TABLENAME+’”  
98.     SET @DECLARE=LEFT(@DECLARE,LEN(@DECLARE)-1)  
99.     SET @INTODECLARE=LEFT(@INTODECLARE,LEN(@INTODECLARE)-1)  
100.     SET @COLUMN2=LEFT(@COLUMN2,LEN(@COLUMN2)-1)  
101.     SET @WHERE= LEFT(@WHERE,LEN(@WHERE)-3)  
102.      
103.     –*******************判断是否还原当前表的最近一次操作*******************           
104.     DECLARE @SQL_ISLAST VARCHAR(MAX)=”  
105.     SET NOCOUNT ON  
106.     DECLARE @maxdate datetime  
107.     SELECT @maxdate=max(updatetime) FROM UPDATE_LOG WHERE TableName=””‘+@TABLENAME+’””  
108.     IF NOT EXISTS(SELECT 1 FROM UPDATE_LOG WHERE UpdateTime=@maxdate AND UPDATEGUID=”””+@NEWID+”””)  
109.     BEGIN  
110.         DECLARE @MAXGUID VARCHAR(50)  
111.         SELECT @MAXGUID=UPDATEGUID FROM UPDATE_LOG WHERE UpdateTime=@maxdate  
112.         PRINT ””此操作并非最近一次操作，请逐步还原,此表最近一次操作的GUID是：””+@MAXGUID  
113.         RETURN  
114.     END  
115.     ”  
116.    
117.     –********************还原insert和update操作用到的SQL*******************  
118.    
119.     DECLARE @SQL_DELETE VARCHAR(MAX)=”  
120.     SET ROWCOUNT 1  –设定相同条件下只删除1行          
121.     DECLARE Cursor_ CURSOR FOR  
122.     SELECT ”+@COLUMN2+” FROM BACKUP_‘+@TABLENAME+’ WHERE UPDATEGUID= ”””+@NEWID+””” AND UpdateType=””INSERT””  
123.     OPEN Cursor_  
124.     DECLARE ”+@DECLARE+”  
125.     FETCH NEXT FROM Cursor_ INTO ”+@INTODECLARE+”  
126.     WHILE @@FETCH_STATUS=0  
127.     BEGIN                    
128.         DELETE FROM ‘+@TABLENAME+’ WHERE ”+@WHERE+”  
129.         FETCH NEXT FROM Cursor_ INTO ”+@INTODECLARE+”  
130.     END  
131.     CLOSE Cursor_  
132.     DEALLOCATE Cursor_  
133.     SET ROWCOUNT 0  
134.     ”  
135.    
136.     –*********************还原delete和update操作用到的SQL*******************  
137.    
138.     DECLARE @SQL_INSERT VARCHAR(MAX)=”  
139.     INSERT INTO ‘+@TABLENAME+’ SELECT ”+@COLUMN1+” FROM BACKUP_‘+@TABLENAME+’ WHERE UPDATEGUID=”””+@NEWID+””” AND UpdateType=””DELETE””  
140.     ”  
141.    
142.     –*********************还原操作之后把备份表和log表的记录删掉*************  
143.    
144.     DECLARE @SQL_DELGUID VARCHAR(MAX)=”  
145.     DELETE FROM BACKUP_‘+@TABLENAME+’ WHERE  UPDATEGUID IN(SELECT UPDATEGUID FROM UPDATE_LOG WHERE UpdateTime>=@maxdate AND TableName=””‘+@TABLENAME+’””)  
146.     DELETE FROM UPDATE_LOG WHERE UpdateTime>=@maxdate AND TableName=””‘+@TABLENAME+’””  
147.     PRINT ””回滚操作执行成功，共恢复 ”+CAST(@ROWCOUNT AS VARCHAR(10))+” 条记录””  
148.     SET NOCOUNT OFF  
149.     ”  
150.    
151.     –*********************执行还原操作的SQL**********************************  
152.    
153.     DECLARE @EXECSQL VARCHAR(500)=”  
154.     DECLARE @SQL VARCHAR(MAX)  
155.     SELECT @SQL=ROLLBACKSQL FROM UPDATE_LOG WHERE UPDATEGUID=”””+@NEWID+”””    
156.     EXEC(@SQL)   
157.     ”  
158.    
159.     –==============================判断执行的哪种操作方式=================================  
160.    
161.     DECLARE @DoType VARCHAR(MAX)=”UPDATE”  
162.     IF NOT EXISTS(SELECT 1 FROM deleted)  
163.         SET @DoType=”INSERT”  
164.     IF NOT EXISTS(SELECT 1 FROM inserted)  
165.         SET @DoType=”DELETE”  
166.     IF NOT EXISTS(SELECT 1 FROM deleted) AND  NOT EXISTS(SELECT 1 FROM inserted)  
167.         RETURN  
168.     IF @DoType=”UPDATE”  
169.     BEGIN  
170.         INSERT INTO [dbo].[UPDATE_LOG]  
171.         SELECT @NEWID,GETDATE(),”‘+@TABLENAME+’”,”UPDATE”,@SQL_ISLAST+@SQL_DELETE+@SQL_INSERT+@SQL_DELGUID,@EXECSQL  
172.         RETURN  
173.     END  
174.     IF @DoType=”DELETE”  
175.     BEGIN  
176.         INSERT INTO [dbo].[UPDATE_LOG]  
177.         SELECT @NEWID,GETDATE(),”‘+@TABLENAME+’”,”DELETE”,@SQL_ISLAST+@SQL_INSERT+@SQL_DELGUID,@EXECSQL  
178.         RETURN  
179.     END  
180.     IF @DoType=”INSERT”  
181.     BEGIN  
182.         INSERT INTO [dbo].[UPDATE_LOG]  
183.         SELECT @NEWID,GETDATE(),”‘+@TABLENAME+’”,”INSERT”,@SQL_ISLAST+@SQL_DELETE+@SQL_DELGUID,@EXECSQL  
184.         RETURN  
185.     END  
186. END  
187.             ’  
188.         EXEC (@SQLTR)  
189.     END  
190. END  
191.    

CREATE PROCEDURE [dbo].[SP_UPDATE_LOG]
    @TABLENAME VARCHAR(50)
AS
BEGIN
    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,Insert
AS
BEGIN 
    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
    END
END
            '
        EXEC (@SQLTR)
    END
END
 

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

[sql] view plain copy print ?

1. CREATE TABLE test(  
2.     [id] [int] NULL,  
3.     [name] [varchar](10) NULL  
4. )   
5.   
6. INSERT INTO test  
7. SELECT 1,‘a’  
8. UNION ALL  
9. SELECT 2,‘b’  
10. UNION ALL  
11. SELECT 3,‘c’  
12. UNION ALL  
13. SELECT 4,‘d’  
14. UNION ALL  
15. SELECT 5,‘a’  
16. UNION ALL  
17. SELECT 6,‘b’  
18.   
19. SELECT * FROM test  

CREATE TABLE test(
    [id] [int] NULL,
    [name] [varchar](10) NULL
) 

INSERT INTO test
SELECT 1,'a'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c'
UNION ALL
SELECT 4,'d'
UNION ALL
SELECT 5,'a'
UNION ALL
SELECT 6,'b'

SELECT * FROM test

检查一下，表建好了：

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

[sql] view plain copy print ?

1. EXEC SP_UPDATE_LOG ‘test’   –给test表建立update回滚日志  
2. SELECT * FROM [dbo].[BACKUP_test]   –test表数据备份  
3. SELECT * FROM [dbo].[UPDATE_LOG]    –update操作记录  

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语句执行，注意要先执行时间最近的操作记录，一步一步还原：

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

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

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