SQL Server追加Delete操作スクロールログ方式
私たちは操作表を誤って削除したり、削除したデータを元に戻したい場合があります。
注意深い友達はbegin tran rollback/comitということでミスを避けるかもしれませんが、これは一番安全ではありません。
もし物事を提出したら、削除したり、提出を忘れたりして、表がロックされることは避けられません。
余計な話はしないでください。次は直接本題に入ります。触発器によってログを削除して、誤って削除して気まずい思いをさせないようにします。
次のsqlは過去にくっついて直接運行して、記憶過程を作ります。
そして私たちはデータを削除します。
そうです。この時にログの表にデータがありました。そして私達はアップロードをします。LOGテーブルのRollBackSQ Lの列に対応する値copyが出てきて実行してください。
しかし、データは復元されました。
最後に、delete日記の紹介は終わりました。唯一の不満はDeleteでしか操作できないです。実はUPDATE操作も同じようにこのようなスクロールログが必要です。
以上は個人の経験ですので、参考にしていただければと思います。間違いがあったり、完全に考えていないところがあれば、教えてください。
注意深い友達はbegin tran rollback/comitということでミスを避けるかもしれませんが、これは一番安全ではありません。
もし物事を提出したら、削除したり、提出を忘れたりして、表がロックされることは避けられません。
余計な話はしないでください。次は直接本題に入ります。触発器によってログを削除して、誤って削除して気まずい思いをさせないようにします。
次の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'
以下はこのSPを実行して、testテーブルにロールバックログを追加します。
EXEC SP_DELETE_LOG 'test'
注意深いあなたは気づきにくくないです。この時、データベースの中には2つの表が多く出るはずです。そして私たちはデータを削除します。
DELETE FROM test WHERE id=1
その二つの表をもう一度確認してください。そうです。この時にログの表にデータがありました。そして私達はアップロードをします。LOGテーブルのRollBackSQ Lの列に対応する値copyが出てきて実行してください。
INSERT INTO test SELECT [id],[name],[msg] FROM BACKUP_test WHERE UPDATEGUID='B0CBBC4F-3432-4D4F-9E17-F17209BF6745'
上記のsqlはGUIDが違います。しかし、データは復元されました。
最後に、delete日記の紹介は終わりました。唯一の不満はDeleteでしか操作できないです。実はUPDATE操作も同じようにこのようなスクロールログが必要です。
以上は個人の経験ですので、参考にしていただければと思います。間違いがあったり、完全に考えていないところがあれば、教えてください。