我们在操作表的时候难免会遇到误删除，或者删掉的数据还想恢复的情况。
也许细心的朋友会用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 操作也同样需要这样的回滚日志。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持www.887551.com。如有错误或未考虑完全的地方，望不吝赐教。