主頁 > 知識庫 > SQL Server 添加Delete操作回滾日志方式

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

熱門標(biāo)簽:在電子版地圖標(biāo)注要收費(fèi)嗎 外呼系統(tǒng)會封嗎 南京電銷外呼系統(tǒng)哪家好 地圖標(biāo)注如何弄全套標(biāo) 武漢AI電銷機(jī)器人 電銷機(jī)器人 深圳 萬利達(dá)綜合醫(yī)院地圖標(biāo)注點(diǎn) 股票配資電銷機(jī)器人 實(shí)體店地圖標(biāo)注怎么標(biāo)

我們在操作表的時候難免會遇到誤刪除,或者刪掉的數(shù)據(jù)還想恢復(fù)的情況。

也許細(xì)心的朋友會用begin tran rollback/commit 這種事務(wù)來避免出現(xiàn)失誤,但這并不是最保險(xiǎn)的。

如果提交了事物發(fā)現(xiàn)刪錯了或者忘記提交從而導(dǎo)致表被鎖,這些問題總是不可避免的。

廢話不多說了,下面直接進(jìn)入正題,通過觸發(fā)器記錄刪除日志,避免誤刪除帶來的尷尬。

下面這段sql粘過去直接運(yùn)行,建立一個存儲過程:

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 觸發(fā)器=========================
	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()
				--==============================將刪掉的數(shù)據(jù)插入備份表============================
				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

接著我們新建一張測試表,并且隨便往表中插入兩組數(shù)據(jù):

 Create table test 
 (
 id int,
 name varchar(10),
 msg varchar(10)
 )
 Insert into test
 Select 1,'aa','hahah'
 Union all 
 Select 2,'bb','heihei'

下面執(zhí)行這個SP,在給test表添加回滾日志:

EXEC SP_DELETE_LOG 'test'

細(xì)心的你不難發(fā)現(xiàn),這時候數(shù)據(jù)庫里面應(yīng)該會多出兩張表:

然后我們刪掉一條數(shù)據(jù):

DELETE FROM test WHERE id=1

再查看那兩張表:

沒錯,這時候日志表里有數(shù)據(jù)了,然后我們把 UPDATE_LOG 表中的 RollBackSQ L這一列對應(yīng)的值copy出來執(zhí)行一下:

INSERT INTO test SELECT [id],[name],[msg] FROM BACKUP_test WHERE UPDATEGUID='B0CBBC4F-3432-4D4F-9E17-F17209BF6745'

別copy我上面這段sql,因?yàn)镚UID肯定是不一樣的!

然而,數(shù)據(jù)恢復(fù)了:

最后,delete日志的介紹就結(jié)束了,唯一的不滿足的是只能作用在Delete 操作,其實(shí)UPDATE 操作也同樣需要這樣的回滾日志。

以上為個人經(jīng)驗(yàn),希望能給大家一個參考,也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方,望不吝賜教。

您可能感興趣的文章:
  • SqlServer2008誤操作數(shù)據(jù)(delete或者update)后恢復(fù)數(shù)據(jù)的方法
  • sqlserver中drop、truncate和delete語句的用法
  • sqlserver中delete、update中使用表別名和oracle的區(qū)別
  • SQLServer 2008中SQL增強(qiáng)之三 Merge(在一條語句中使用Insert,Update,Delete)

標(biāo)簽:武威 廣東 濟(jì)寧 安徽 臺州 汕頭 泰安 濟(jì)源

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《SQL Server 添加Delete操作回滾日志方式》,本文關(guān)鍵詞  SQL,Server,添加,Delete,操作,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《SQL Server 添加Delete操作回滾日志方式》相關(guān)的同類信息!
  • 本頁收集關(guān)于SQL Server 添加Delete操作回滾日志方式的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章