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

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

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