怎么在SQL Server中利用Delete操作回滚日志

怎么在SQL Server中利用Delete操作回滚日志？很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

建立一个存储过程：

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'

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。