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

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

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

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

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

  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 @ 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

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

   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'

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

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

然后我们删掉一条数据:

  DELETE FROM test WHERE id=1

再查看那两张表:

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

没错,这时候日志表里有数据了,然后我们把 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肯定是不一样的!

然而,数据恢复了:

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

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

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

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注