92 lines
7.9 KiB
Transact-SQL
92 lines
7.9 KiB
Transact-SQL
USE [VEPROMS]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[addItemSiblingBefore] Script Date: 01/25/2010 09:38:28 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
ALTER PROCEDURE [dbo].[addItemSiblingBefore]
|
||
(
|
||
@ItemID int=null, @Number nvarchar(30)=null, @Text nvarchar(MAX)=null, @FormatID int=null, @Config nvarchar(MAX)=null,
|
||
@Type int=null, @DTS datetime, @UserID nvarchar(100), @newItemID int output
|
||
)
|
||
WITH EXECUTE AS OWNER
|
||
AS
|
||
BEGIN TRY -- Try Block
|
||
BEGIN TRANSACTION
|
||
DECLARE @PreviousID int, @ContentID int, @newLastChanged timestamp, @NewType int
|
||
SELECT @PreviousID = [PreviousID], @NewType=ISNULL(@Type,[Type])
|
||
FROM [ITEMS] II
|
||
JOIN [CONTENTS] CC ON CC.[ContentID]=II.[CONTENTID]
|
||
WHERE [ItemID]=@ItemID
|
||
EXECUTE AddContent @Number, @Text, @NewType, @FormatID, @Config, @DTS, @UserID, @ContentID output, @newLastChanged output
|
||
EXECUTE AddItem @PreviousID, @ContentID, @DTS, @UserID , @newItemID output, @newLastChanged output
|
||
UPDATE [ITEMS] SET [PreviousID]=@newItemID where [ItemID]=@ItemID -- Should be UpdateItem
|
||
UPDATE [PARTS] SET [ItemID]=@newItemID where [ItemID]=@ItemID -- Should be UpdatePart
|
||
IF @PreviousID is null -- The step is replacing the first step
|
||
BEGIN
|
||
-- Update content records for the transitions
|
||
Update CC
|
||
Set Text = DBO.vefn_FixTransitionText(Text,TT.TransitionID,TT.TranType,TT.ToID,TT.RangeID,@ItemID,@newItemID)
|
||
From CONTENTS CC
|
||
JOIN Transitions TT ON TT.FromID = CC.ContentID
|
||
WHERE TT.ToID = @ItemID OR TT.RangeID = @ItemID
|
||
-- Update transitions that pointed to @ItemID to point to @newItemID
|
||
Update TRANSITIONS
|
||
Set ToID = CASE ToID WHEN @ItemID THEN @newItemID ELSE ToID END,
|
||
RangeID = CASE RangeID WHEN @ItemID THEN @newItemID ELSE RangeID END
|
||
WHERE ToID = @ItemID OR RangeID = @ItemID
|
||
END
|
||
ELSE -- Check for Transitions that point to the Next Step
|
||
BEGIN
|
||
-- Get a list of Transitions which need to change children of @ItemID that point to @NextID
|
||
DECLARE @NextStepTransitions TABLE
|
||
(
|
||
[TransitionID] int PRIMARY KEY,
|
||
[FromID] [int],
|
||
[ToID] [int],
|
||
[RangeID] [int],
|
||
[TranType] [int],
|
||
[Config] [nvarchar](max)
|
||
)
|
||
Insert into @NextStepTransitions select * from vefn_NextStepTransitions(@ItemID,@PreviousID)
|
||
IF (SELECT COUNT(*) from @NextStepTransitions) > 0 -- Next Step Transitions
|
||
BEGIN
|
||
-- Update all transitions in the list to point to @newItemID
|
||
Update TT
|
||
Set TT.ToID = CASE TT.ToID WHEN @ItemID THEN @newItemID ELSE TT.ToID END,
|
||
TT.RangeID = CASE TT.RangeID WHEN @ItemID THEN @newItemID ELSE TT.RangeID END
|
||
From TRANSITIONS TT
|
||
JOIN @NextStepTransitions NS ON NS.TransitionID = TT.TransitionID
|
||
-- Update content records for the transitions
|
||
Update CC
|
||
Set Text = DBO.vefn_FixTransitionText(Text,NS.TransitionID,NS.TranType,NS.ToID,NS.RangeID,@ItemID,@newItemID)
|
||
From CONTENTS CC
|
||
JOIN @NextStepTransitions NS ON NS.FromID = CC.ContentID
|
||
END
|
||
END
|
||
-- Add 'Verification Required' AnnotationType
|
||
DECLARE @typeID int
|
||
SELECT @typeID = TypeID from AnnotationTypes where Name = 'Verification Required'
|
||
IF(@typeID IS NULL)
|
||
BEGIN
|
||
INSERT INTO [AnnotationTypes] ([Name],[UserID]) VALUES ('Verification Required','Volian')
|
||
SELECT @typeID = SCOPE_IDENTITY()
|
||
END
|
||
-- Add "Verification Required" Annotation for each Transition that points to @newItemID or @NextID
|
||
INSERT INTO [Annotations] ([ItemID],[TypeID],[SearchText],[UserID])
|
||
SELECT ItemID, @typeID,'Verify Transition Destination',@UserID
|
||
FROM Items where CONTENTID in (SELECT FromID FROM TRANSITIONS
|
||
where ToID IN(@ItemID,@newItemID) OR RangeID IN(@ItemID,@newItemID))
|
||
-- Transition Text gets updated in ItemInsertExt.cs
|
||
IF( @@TRANCOUNT > 0 ) COMMIT
|
||
PRINT 'Sibling Added Before ' + ltrim(str(@newItemID))
|
||
EXECUTE GetItem @newItemID
|
||
END TRY
|
||
BEGIN CATCH -- Catch Block
|
||
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
|
||
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
|
||
EXEC vlnErrorHandler
|
||
END CATCH
|