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

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

也许细心的朋友会用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 @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

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

 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 操作也同样需要这样的回滚日志。

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