E9流程回收站相关说明

说明：E9回收站不会自动进行清理，标准产品上不提供删除回收站相应流程功能。

1、相关的表查询SQL

select * from workflow_nownode_dellog  
select * from Workflow_SubwfRequest_dellog
select * from workflow_requestbase_dellog;
select * from workflow_curroperator_dellog;
select * from workflow_requestdeletelog;
select * from workflow_requestLog_dellog;

涉及表

workflow_logviewusers_dellog 
workflow_nownode_dellog  
Workflow_SubwfRequest_dellog
workflow_requestbase_dellog;
workflow_curroperator_dellog;
workflow_requestdeletelog;
workflow_requestLog_dellog;

解决方案

1、总体目标

在Microsoft SQL Server (MSSQL)中，不支持一条DELETE​语句直接删除多个表中的数据。SQL Server的DELETE​命令只能针对单个表操作。如果要删除与某个主键值关联的所有记录，你将需要为每张表编写一个DELETE​语句。

如下：

-- 删除B表中与A表主键关联的数据
DELETE B
FROM B
INNER JOIN A ON B.ForeignKey = A.PrimaryKey;
-- 删除C表中与A表主键关联的数据
DELETE C
FROM C
INNER JOIN A ON C.ForeignKey = A.PrimaryKey;
-- 最后删除A表中的主键记录
DELETE FROM A WHERE PrimaryKey = @SomeValue;

2、实施方案

对相关表进行分析，判定每个表都是通过requestid​进行关联。

2-1、原始数据备份

2-2、数据分析

2-2-1、判定时间（超过3个月）

根据workflow_requestdeletelog​来判断删除时间，其他表关联workflow_requestdeletelog​进行删除。

SELECT *  from workflow_requestdeletelog bp 
where bp.operate_date <= DATEADD(month, -3, GETDATE());

2-2-2、关联查询

对相关表进行关联workflow_requestdeletelog​查询

SELECT * from workflow_requestdeletelog cp
where cp.request_id in(SELECT bp.request_id  from workflow_requestdeletelog bp 
where bp.operate_date <= DATEADD(month, -3, GETDATE()))

2-2-3、执行删除

delete from workflow_requestLog_dellog 
where requestid in (SELECT request_id  from workflow_requestdeletelog bp 
where bp.operate_date <= DATEADD(month, -3, GETDATE()))

3、附录

--根据requestid删除回收站指定流程
delete from workflow_requestLog_dellog where requestid=?;
delete from  workflow_requestbase_dellog where requestid=?;
delete from workflow_curroperator_dellog where requestid=?;
delete from workflow_requestdeletelog where request_id=?;
delete from workflow_logviewusers_dellog where request_id=?;
delete from workflow_nownode_dellog where request_id=?;
--清理整个回收站
delete from workflow_requestbase_dellog;
delete from workflow_curroperator_dellog;
delete from workflow_requestdeletelog;
delete from workflow_requestLog_dellog;
delete from workflow_logviewusers_dellog 
delete from workflow_nownode_dellog 

谨慎操作删除，建议操作前，进行相应的数据备份后执行。