一次性删除某个项目的所有事务 --URTracker 事务跟踪系统(Issue Tracking System) 基于web的BUG跟踪、问题跟踪、任务跟踪、服务跟踪、需求跟踪软件--URTracker事务跟踪系统: 基于web的问题管理平台, BUG跟踪、任务跟踪、服务跟踪、需求跟踪、流程跟踪、Issue Tracking、ActionTracking软件

[#197] 一次性删除某个项目的所有事务

【内容】

本脚本适用于URTracker3.3版。

为避免出现误操作的情况，请您在执行本脚本之前，先备份数据库。

------------------------begin
declare @ProjectID int
SET @ProjectID = 项目ID

Delete From Pts_ProblemStateRecord Where ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID )

Delete From Pts_RecordAttachments Where RecordID in
(Select RecordID From Pts_Records Where ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID ))

Delete From Pts_Records Where ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

Delete From Pts_ProblemAttachments Where ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

DELETE FROM Pts_ProblemHistoryRecord WHERE ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

Delete FROM Pts_ProblemHistory WHERE ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

DELETE FROM Pts_ItemReadHistory WHERE ItemType=1 AND ItemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

Delete FROM Pts_ProblemRelatedArticles WHERE ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

Delete FROM Pts_RelatedUsers WHERE ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

Delete FROM Pts_ProblemVisitHistory WHERE ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

Delete FROM Pts_ProblemHistory WHERE ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

Delete FROM Pts_ProblemRelations WHERE ProblemID_1 IN
    (Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID) OR ProblemID_2 IN
    (Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID )

DELETE FROM Pts_ProblemShiftHistory WHERE ProblemID IN (Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

DELETE FROM Pts_ProblemTimers1 WHERE ProblemID IN (Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

DELETE FROM Pts_ProblemVisitHistory WHERE ProblemID IN (Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

DELETE FROM Pts_RelatedUsers WHERE ProblemID IN (Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

--DELETE FROM Pts_Subscription WHERE TargetType = 1 AND TargetID = @ProjectID
DELETE FROM Pts_Subscription WHERE TargetType = 2 AND TargetID IN (Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

DELETE FROM Pts_ZCategoryProblem WHERE ProblemID in
(Select ProblemID From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID)

Delete From Pts_Problems Where ProjectID = @ProjectID or OriginProjectID=@ProjectID
exec sp_Pts_ComputeProjectCounter @ProjectID

-- 下面一行用于重置项目内的事务编码，如果需要重新从1开始编码，请删除前面的“--”
Update Pts_Projects Set NewProblemIndex=1 Where ProjectID=@ProjectID

--------------------------------------------end