SQL Server 中删除前备份数据的“正确”姿势

目录

1、先看完整代码

2、代码解析

A.创建数据备份表，涉及几个关键字段：

B.取得要删除的记录数据，放到一个JSON传里

C.将JSON数据写入到A中的表里 

D.删除原表中的数据，并返回结果

E. DEMO 调用截图

3、后续：当出现“误删”需要恢复数据是怎么办？

总结

---

再使用数据库处理数据时，有时为了防止出现系统逻辑错误或操作人员的误操作，有时需要再删除数据时将数据备份，备份的操作有很多种方法，如：1、记录打上删除标志，但不真实删除，2、或者将数据备份到其他表再删除。每种方法各有利弊，1中数据在同一表中，恢复方便，但查询极为不便，需要增加一个删除标志条件，2中的恢复不变，但数据干净，查询也简单，具体使用那种，取决于中间层或业务逻辑处理的方式。本文介绍的是第2种，特别是JSON格式的数据已经广为各种DBMS支持的前提下。

1、先看完整代码

if (object_id('sp_delete', 'P') is not null)
    drop proc sp_delete
go


create procedure sp_delete(
	@table_name nvarchar(1000),
	@id varchar(50)
)
as
	declare 
		@m_table_name nvarchar(1000),
		@m_id varchar(50);

	declare @sql varchar(max);
	set @m_table_name = replace(@table_name, '''', '''''');
	set @m_id = replace(@id, '''', '''''');

	-- 表需要预先建好
	/*
	if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'deleted_data')
	begin
		set @sql = '
		-- drop table deleted_data
		create table deleted_data (
		   id                   varchar(50)          not null,
		   table_name           varchar(1000)        null,
		   row_id               varchar(50)          null,
		   row_data             varchar(max)         null,
		   version              bigint               null,
		   del_flag             tinyint              null,
		   create_time          datetime             null,
		   modify_time          datetime             null,
		   create_user_id       varchar(50)          null,
		   modify_user_id       varchar(50)          null,
		   constraint PK_DELETED_DATA_0001 primary key nonclustered (id)
		)
		'
		exec( @sql )
	end
	-- */

	if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @table_name)
	begin
		select 'error' as result, '表[' + @m_table_name + ']不存在' as result_desc
	end else begin
		set @sql = '
		
		declare @json varchar(max);
		set @json = (select * from [' + @m_table_name + '] where id = ''' + @m_id + ''' for json auto);
		print @json
		if (@json is not null)
		begin
			insert into deleted_data (
			   id            ,
			   table_name    ,
			   row_id        ,
			   row_data      ,
			   version       ,
			   del_flag      ,
			   create_time   ,
			   modify_time   ,
			   create_user_id,
			   modify_user_id
			)
			select NEWID(), ''' + @m_table_name + ''', ''' + @m_id + ''', @json, 1, 0, getdate(), null, ''SYS'', null;
			--end
			delete from [' + @m_table_name + '] where id = ''' + @m_id + ''';
		end
		select ''success'' as result, ''备份成功!删除'' + cast(@@ROWCOUNT as varchar)+ ''条记录'' as result_desc
		'
		print @sql
		exec(@sql);
	end
go

2、代码解析

假设：每个表有一个id字段

删除记录时，不是直接调用delete from 表 where id = xxx，改为调用以上存储过程：

A.创建数据备份表，涉及几个关键字段：

id                   varchar(50)          not null, -- ID
table_name           varchar(1000)        null,     -- 删除备份的表名称
row_id               varchar(50)          null,     -- 要删除的记录ID
row_data             varchar(max)         null,     -- 要删除的记录数据 JSON 格式
create_time          datetime             null,     -- 删除的时间
create_user_id       varchar(50)          null,     -- 谁删除的

B.取得要删除的记录数据，放到一个JSON传里

declare @json varchar(max);
set @json = (select * from [' + @m_table_name + '] where id = ''' + @m_id + ''' for json auto);
print @json

C.将JSON数据写入到A中的表里 

-- 表为空才备份+删除
if (@json is not null)
		begin
			insert into deleted_data (
			   id            ,
			   table_name    ,
			   row_id        ,
			   row_data      ,
			   version       ,
			   del_flag      ,
			   create_time   ,
			   modify_time   ,
			   create_user_id,
			   modify_user_id
			)
			select NEWID(), ''' + @m_table_name + ''', ''' + @m_id + ''', @json, 1, 0, getdate(), null, ''SYS'', null;
			--end
			delete from [' + @m_table_name + '] where id = ''' + @m_id + ''';
		end

D.删除原表中的数据，并返回结果

		select ''success'' as result, ''备份成功!删除'' + cast(@@ROWCOUNT as varchar)+ ''条记录'' as result_desc

E. DEMO 调用截图

3、后续：当出现“误删”需要恢复数据是怎么办？

当出现“误删”需要恢复数据是怎么办，如何将备份的JSON格式的数据恢复到原来的表里，请参考我们的另外一篇文章：

使用JSON实现SQL Server少量数据传递（导入导出）

https://mp.csdn.net/mp_blog/creation/editor/119721347

总结

文中方法数据采用JSON，格式相对简单易懂，也没那么多数据冗余，开发过程不必计较删除标志，易维护，省时省力