-- 除非有特殊的需要，请不要尝试本文的脚本
declare @ProblemID int
SET @ProblemID=要删除事务的ID
declare @ChildCount int
select @ChildCount = COUNT(1) FROM Pts_Problems WHERE ParentID=@ProblemID
if (@ChildCount = 0) --make sure no child
begin
--Begin Try
Begin transaction
Declare @ParentID int
select @ParentID=ParentID FROM Pts_Problems WHERE ProblemID = @ProblemID
Delete From Pts_RecordAttachments Where RecordID in
(Select RecordID From Pts_Records Where ProblemID = @ProblemID)
Delete From Pts_Records Where ProblemID = @ProblemID
Delete From Pts_ProblemAttachments Where ProblemID = @ProblemID
Delete From Pts_ProblemStateRecord Where ProblemID = @ProblemID
DELETE FROM Pts_ProblemHistoryRecord WHERE ProblemID=@ProblemID
Delete From Pts_ProblemHistory Where ProblemID = @ProblemID
Delete From Pts_ProblemRelations Where ProblemID_1 = @ProblemID OR ProblemID_2 = @ProblemID
Delete From Pts_ProblemShiftHistory Where ProblemID = @ProblemID
Delete From Pts_ProblemVisitHistory Where ProblemID = @ProblemID
Delete FROM Pts_ProblemRelatedArticles WHERE ProblemID = @ProblemID
Delete FROM Pts_Coworkers WHERE ProblemID=@ProblemID
DELETE FROM Pts_ItemReadHistory WHERE ItemType=1 AND ItemID=@ProblemID
Delete FROM Pts_RelatedUsers WHERE ProblemID =@ProblemID
DELETE FROM Pts_ZCategoryProblem WHERE ProblemID=@ProblemID
Delete From Pts_Problems Where ProblemID = @ProblemID
commit transaction
--End Try
--Begin Catch
-- Rollback TransAction
--End Catch
DECLARE @TotalChildNum int
DECLARE @TotalClosedChild int
if @ParentID > 0
BEGIN
SELECT @TotalChildNum = count(1),@TotalClosedChild=sum(case when IsClosed=1 then 1 else 0 end)
FROM v_Pts_Problems
WHERE ParentID=@ParentID
if @TotalClosedChild is null
begin
set @TotalClosedChild = 0
end
UPDATE Pts_Problems Set TotalChildNum=@TotalChildNum,ClosedChildNum=@TotalClosedChild WHERE ProblemID=@ParentID
END
end