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
|