SQL Server添加Delete操作回滚日志
来源:互联网 发布:淘宝网卫裤 编辑:程序博客网 时间:2024/06/09 23:32
我们在操作表的时候难免会遇到误删除,或者删掉的数据还想恢复的情况。也许细心的朋友会用begin tran rollback/commit 这种事务来避免出现失误,但这并不是最保险的。如果提交了事物发现删错了或者忘记提交从而导致表被锁,这些问题总是不可避免的。废话不多说了,下面直接进入正题,通过触发器记录删除日志,避免误删除带来的尴尬。
下面这段sql粘过去直接运行,建立一个存储过程:
CREATE PROCEDURE [dbo].[SP_DELETE_LOG] @TABLENAME VARCHAR(50)ASBEGINSET NOCOUNT ON; IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = @TABLENAME AND TYPE = 'U' )BEGINPRINT'ERROR:not exist table '+@TABLENAMERETURNENDIF (@TABLENAME LIKE'BACKUP_%' OR @TABLENAME='UPDATE_LOG' )BEGIN--PRINT'ERROR:not exist table '+@TABLENAMERETURNEND--================================判断是否存在 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 FORSELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=@TABLENAMEOPEN test_CursorDECLARE @SQLTB NVARCHAR(MAX)=''DECLARE @COLUMN_NAME NVARCHAR(50),@DATA_TYPE VARCHAR(20),@CHARACTER_MAXIMUM_LENGTH INTFETCH NEXT FROM test_Cursor INTO @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTHWHILE @@FETCH_STATUS=0BEGINSET @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_LENGTHENDSET @SQLTB='CREATE TABLE BACKUP_'+@TABLENAME+' (UpdateGUID varchar(36),'+SUBSTRING(@SQLTB,1,LEN(@SQLTB)-1)+')'EXEC (@SQLTB)CLOSE test_Cursor DEALLOCATE test_CursorEND--======================================判断是否存在 DELETE 触发器=========================IF NOT EXISTS(SELECT * FROM sys.objects WHERE NAME = 'tg_'+@TABLENAME+'_Delete' AND TYPE = 'TR')BEGINDECLARE @SQLTR NVARCHAR(MAX)SET @SQLTR='CREATE TRIGGER tg_'+@TABLENAME+'_DeleteON '+@TABLENAME+'AFTER deleteAS BEGINSET 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.columnsWHERE 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
接着我们新建一张测试表,并且随便往表中插入两组数据:
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表添加回滚日志:
EXEC SP_DELETE_LOG 'test'
细心的你不难发现,这时候数据库里面应该会多出两张表:
然后我们删掉一条数据:
DELETE FROM test WHERE id=1
再查看那两张表:
没错,这时候日志表里有数据了,然后我们把 UPDATE_LOG 表中的 RollBackSQ L这一列对应的值copy出来执行一下:
INSERT INTO test SELECT [id],[name],[msg] FROM BACKUP_test WHERE UPDATEGUID='B0CBBC4F-3432-4D4F-9E17-F17209BF6745'
别copy我上面这段sql,因为GUID肯定是不一样的!
然而,数据恢复了:
最后,delete日志的介绍就结束了,唯一的不满足的是只能作用在Delete 操作,其实UPDATE 操作也同样需要这样的回滚日志。
今天就到这里结束了,下一篇我会介绍关于UPDATE的回滚日志的建立。Bye Bye~~~
阅读全文
2 0
- SQL Server添加Delete操作回滚日志
- SQLServer添加UPDATE回滚日志(update/delete/insert)
- SQL SERVER回滚恢复误操作的数据
- sql server 事务回滚
- sql server 事务回滚
- Sql server 事务回滚
- SQL Server TransAction 全部回滚
- SQL Server 事务及回滚事务
- SQL Server TransAction 全部回滚
- SQL Server TransAction 全部回滚
- C# 实现 SQL Server 事务回滚
- SQL Server存储过程回滚
- SQL SERVER 2008R2从日志中恢复Delete数据
- sql根据日志回滚(摘抄于百度)
- Mysql数据库日志,备份及回滚操作
- python日志回滚
- Cascade delete - SQL Server
- 存储过程调用"事务回滚" (SQL server)
- 序列化protobuf
- CSDN博客不能正常发布的问题
- sort-排序
- Codeforces Combination Lock
- 51nod 1110 距离之和最小 V3
- SQL Server添加Delete操作回滚日志
- 来听大师讲设计(下)
- Redis设计
- Python中的repr()函数
- 链表——带环问题
- 关于使用PDO连接数据库的一些基本操作
- 【大数据部落】如何使用rjags R2jags来建立贝叶斯模型
- 判断已打开线程与本进程对比
- 这几个软件你可能没听过,但真的好用到爆!