August 25, 2009 at 7:51 pm
When an INSTEAD OF trigger on a table calls a stored procedure that contains SQL which then acts on the original table, is the INSTEAD OF trigger re-fired creating a loop? Basically, I'
d like to encapsulate the actual SQL which INSERTs, UPDATEs or DELTEs records from within the stored procedure.
August 26, 2009 at 9:31 am
Can you explain why you are using an Instead Of Trigger? I have seen very few instances where they are necessary. I'm not saying it's not necessary in your case, I'm just wondering what the case is.
Typically actions on a table from within a trigger (AFTER in my experience) do not cause the trigger to re-fire. I'm not sure in the case of INSTEAD OF triggers though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 26, 2009 at 1:05 pm
The table in question contains detail records for a master which need a custom sort order determined by the user. Regardless of the specific physical order of the records, the value in the column used to sort the records should be 1 to [max] across all of the records without any gaps. If the user changes the record at position '5', the record value is changed and the other's all changed accordingly. The same for an insert or delete.
I deliberately avoided using an approach that merely increments or decrements the value given that such an approach assumes that there are no gaps in the sequencing. The approach that I did take cleans up the records first and then manipulates everything so that no gaps ever appear and the record is given the correct value.
Also, I wanted to go with a single stored procedure so the approach needed to handle all three scenarios - INSERT, DELETE, UPDATE.
The code does seem bulky, but at least it works. I would have preferred to use a SQL Statement that takes the value returned for the row by Row_Number() and update the Priority column with that so that its all done in just one or two statements, but could never figure out how to do that.
The key requirement was to insure that when the child records are examined, that no gaps occur and that the range be as condense as possible. The values are displayed on a ASP.NET repeater via a drop down list.
USE [CentralEventInformation]
GO
/****** Object: StoredProcedure [dbo].[sp_manageDelegatePriority] Script Date: 08/26/2009 14:59:29 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_manageDelegatePriority]
(
@contactId integer,
@updatedRecord integer,
@NewPriority decimal(7,1),
@OldPriority decimal(7,1) = Null
)
AS
BEGIN
DECLARE @tempTable Table (Id int, RowNumber int, DelegateContactId int, Priority decimal(7,1))
--STEP 0: Insert the record if its a new one
IF @OldPriority Is Null
BEGIN
SET @OldPriority = 0
INSERT INTO Delegates (ContactId, DelegateContactId, Priority)
SELECT @contactId AS ContactId, @updatedRecord AS DelegateContactId, @OldPriority as Priority;
END
--STEP 1: Grab the records into a table variable
INSERT INTO @tempTable (RowNumber, DelegateContactId, Id, Priority)
SELECT ROW_NUMBER() Over(Order by Priority) as RowNumber, DelegateContactId, Id, Priority FROM Delegates WHERE ContactId = @contactId ORDER BY Priority
--STEP 2: Fix any gaps in the records
UPDATE @tempTable SET Priority = RowNumber
--STEP 3: Update the record's priority, Updating the value to .1 ensures that the record appears in the correct ordinal position
If @OldPriority @NewPriority
BEGIN
UPDATE @tempTable SET Priority = @NewPriority - .1 WHERE DelegateContactId = @updatedRecord
END
--STEP 4A: Change the priority of the records to the negative, so that the next step can add the records in the proper order and that we can
-- identify the set of records that have the correct priority
UPDATE @tempTable SET Priority = Priority * -1
--STEP 4B: Reload the temp table in the correct order
INSERT INTO @tempTable (RowNumber, DelegateContactId, Id, Priority)
SELECT ROW_NUMBER() Over(Order by Priority DESC) as RowNumber, DelegateContactId, Id, Priority * -1 FROM @tempTable
--STEP 5: Delete the unneeded records
DELETE @tempTable WHERE Priority < 0
--STEP 6: Update the records in the physical table
UPDATE Delegates SET Priority = (SELECT RowNumber FROM @tempTable WHERE Id=Delegates.Id) WHERE Delegates.Id = Id
End
GO
USE [CentralEventInformation]
GO
/****** Object: Table [dbo].[Delegates] Script Date: 08/26/2009 15:00:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Delegates](
[Id] [int] IDENTITY(1000,1) NOT NULL,
[ContactId] [int] NOT NULL,
[DelegateContactId] [int] NOT NULL,
[Priority] [decimal](7, 1) NOT NULL,
[ExpirationDate] [date] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Delegates] ADD CONSTRAINT [DF_Delegates_Priority] DEFAULT ((0)) FOR [Priority]
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply