服务器之家

服务器之家 > 正文

SQL Server 添加Delete操作回滚日志方式

时间:2021-02-21 16:52     来源/作者:Wikey_Zhang

我们在操作表的时候难免会遇到误删除,或者删掉的数据还想恢复的情况。

也许细心的朋友会用begin tran rollback/commit 这种事务来避免出现失误,但这并不是最保险的。

如果提交了事物发现删错了或者忘记提交从而导致表被锁,这些问题总是不可避免的。

废话不多说了,下面直接进入正题,通过触发器记录删除日志,避免误删除带来的尴尬。

下面这段sql粘过去直接运行,建立一个存储过程:

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
CREATE PROCEDURE [dbo].[SP_DELETE_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(1000)
        )
    --=================================判断是否存在 BACKUP_ 表================================
    IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = 'BACKUP_'+@TABLENAME AND TYPE = 'U')
    BEGIN
        --DECLARE @SQL VARCHAR(500)
        --SET @SQL='SELECT TOP 1 NEWID() AS [UpdateGUID],* INTO BACKUP_'+@TABLENAME+' FROM '+ @TABLENAME+'
        --       DELETE FROM BACKUP_'+@TABLENAME
        --SELECT @SQL
        --EXEC(@SQL)
        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),'+SUBSTRING(@SQLTB,1,LEN(@SQLTB)-1)+')'
        EXEC (@SQLTB)
        CLOSE test_Cursor
        DEALLOCATE test_Cursor
    END
    --======================================判断是否存在 DELETE 触发器=========================
    IF NOT EXISTS(SELECT * FROM sys.objects WHERE NAME = 'tg_'+@TABLENAME+'_Delete' AND TYPE = 'TR')
    BEGIN
        DECLARE @SQLTR NVARCHAR(MAX)
        SET @SQLTR='
            CREATE TRIGGER tg_'+@TABLENAME+'_Delete
                ON '+@TABLENAME+'
                AFTER delete
            AS
            BEGIN  
                SET NOCOUNT ON;
                --==============================获取GUID==========================================
                DECLARE @NEWID VARCHAR(36)=NEWID()
                --==============================将删掉的数据插入备份表============================
                INSERT INTO [dbo].[BACKUP_'+@TABLENAME+']
                SELECT @NEWID,* FROM deleted
                --==============================记录日志和回滚操作的SQL===========================
                --*********************生成列名**********************
                DECLARE @COLUMN NVARCHAR(MAX)=''''
                SELECT @COLUMN+='',[''+COLUMN_NAME+'']'' FROM INFORMATION_SCHEMA.columns
                WHERE TABLE_NAME='''+@TABLENAME+'''
                AND COLUMNPROPERTY(OBJECT_ID('''+@TABLENAME+'''),COLUMN_NAME,''IsIdentity'')<>1 --非自增字段
                SET @COLUMN=SUBSTRING(@COLUMN,2,LEN(@COLUMN))
                INSERT INTO [dbo].[UPDATE_LOG]
                SELECT @NEWID,GETDATE(),'''+@TABLENAME+''',''DELETE'',''INSERT INTO '+@TABLENAME+' SELECT ''+@COLUMN+'' FROM BACKUP_'+@TABLENAME+' WHERE UPDATEGUID=''''''+@NEWID+''''''''
            END
            '
        EXEC(@SQLTR)
    END
END

接着我们新建一张测试表,并且随便往表中插入两组数据:

?
1
2
3
4
5
6
7
8
9
10
Create table test
(
id int,
name varchar(10),
msg varchar(10)
)
Insert into test
Select 1,'aa','hahah'
Union all
Select 2,'bb','heihei'

下面执行这个SP,在给test表添加回滚日志:

?
1
EXEC SP_DELETE_LOG 'test'

细心的你不难发现,这时候数据库里面应该会多出两张表:

SQL Server 添加Delete操作回滚日志方式

然后我们删掉一条数据:

?
1
DELETE FROM test WHERE id=1

再查看那两张表:

SQL Server 添加Delete操作回滚日志方式

没错,这时候日志表里有数据了,然后我们把 UPDATE_LOG 表中的 RollBackSQ L这一列对应的值copy出来执行一下:

?
1
INSERT INTO test SELECT [id],[name],[msg] FROM BACKUP_test WHERE UPDATEGUID='B0CBBC4F-3432-4D4F-9E17-F17209BF6745'

别copy我上面这段sql,因为GUID肯定是不一样的!

然而,数据恢复了:

SQL Server 添加Delete操作回滚日志方式

最后,delete日志的介绍就结束了,唯一的不满足的是只能作用在Delete 操作,其实UPDATE 操作也同样需要这样的回滚日志。

以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。如有错误或未考虑完全的地方,望不吝赐教。

原文链接:https://blog.csdn.net/Wikey_Zhang/article/details/73648156

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
返回顶部