我们在操作表的时候难免会遇到误删除，或者删掉的数据还想恢复的情况。
也许细心的朋友会用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)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(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 触发器========================= 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) ENDEND |
接着我们新建一张测试表，并且随便往表中插入两组数据：
|
1
2
3
4
5
6
7
8
9
10
|
Create table test (id int,name varchar(10),msg varchar(10))Insert into testSelect 1,'aa','hahah'Union allSelect 2,'bb','heihei' |
下面执行这个SP,在给test表添加回滚日志：
|
1
|
EXEC SP_DELETE_LOG 'test' |
细心的你不难发现，这时候数据库里面应该会多出两张表：
然后我们删掉一条数据：
|
1
|
DELETE FROM test WHERE id=1 |
再查看那两张表：
没错，这时候日志表里有数据了，然后我们把 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肯定是不一样的！
然而，数据恢复了：
最后，delete日志的介绍就结束了，唯一的不满足的是只能作用在Delete 操作，其实UPDATE 操作也同样需要这样的回滚日志。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/Wikey_Zhang/article/details/73648156
