SQL Server 添加Delete操作回滾日志方式
我們?cè)诓僮鞅淼臅r(shí)候難免會(huì)遇到誤刪除，或者刪掉的數(shù)據(jù)還想恢復(fù)的情況。
也許細(xì)心的朋友會(huì)用begin tran rollback/commit 這種事務(wù)來(lái)避免出現(xiàn)失誤，但這并不是最保險(xiǎn)的。
如果提交了事物發(fā)現(xiàn)刪錯(cuò)了或者忘記提交從而導(dǎo)致表被鎖，這些問(wèn)題總是不可避免的。
廢話(huà)不多說(shuō)了，下面直接進(jìn)入正題，通過(guò)觸發(fā)器記錄刪除日志，避免誤刪除帶來(lái)的尷尬。
下面這段sql粘過(guò)去直接運(yùn)行，建立一個(gè)存儲(chǔ)過(guò)程：
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
接著我們新建一張測(cè)試表，并且隨便往表中插入兩組數(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í)行這個(gè)SP,在給test表添加回滾日志：
EXEC SP_DELETE_LOG 'test'
細(xì)心的你不難發(fā)現(xiàn)，這時(shí)候數(shù)據(jù)庫(kù)里面應(yīng)該會(huì)多出兩張表：
然后我們刪掉一條數(shù)據(jù)：
DELETE FROM test WHERE id=1
再查看那兩張表：
沒(méi)錯(cuò)，這時(shí)候日志表里有數(shù)據(jù)了，然后我們把 UPDATE_LOG 表中的 RollBackSQ L這一列對(duì)應(yīng)的值copy出來(lái)執(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é)束了，唯一的不滿(mǎn)足的是只能作用在Delete 操作，其實(shí)UPDATE 操作也同樣需要這樣的回滾日志。
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持本站。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信