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

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

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

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

下面這段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 觸發(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