Hi,
I have trigger that enforces the creation of a sortorder that is always
1 digit higher than the current highest on Inserts.
This trigger works great if I add one row at a time so I think the
logic is sound. However, I have a Stored Procedure that copies a bunch
of rows into this table and all of the SortOrder values come up as 0.
This stored procedure is doing an "Insert Into" and will insert
numerous rows (10-20) at once.
Since these rows are being inserted is it possible that this trigger
doesn't see the new rows? Is it a timing thing?
Thanks - trigger is below
-------------------------------------------------
ALTER TRIGGER dbo.tblActiveStep_SortOrder
ON dbo.tblActiveStep
FOR INSERT
AS
-- Declare procedure level constants / variables / objects
----------------------------------------------------------------------------
DECLARE @intNextSortOrderVal INT
SET NOCOUNT ON
-- Get the MAXimum sort value for steps in Pattern being added
-- and increment by 1
----------------------------------------------------------------------------
BEGIN
SELECT
@intNextSortOrderVal= MAX(intSortOrder) + 1 FROM tblActiveStep
WHERE
tblActiveStep.intActivePatternID
IN
(SELECT inserted.intActivePatternID FROM inserted)
IF @intNextSortOrderVal IS NULL
SELECT @intNextSortOrderVal = 0
-- Set the intSortOrder Value with new calculated value
----------------------------------------------------------------------------
UPDATE
tblActiveStep SET intSortOrder = @intNextSortOrderVal
WHERE
tblActiveStep.intActivePatternID
IN
(SELECT inserted.intActivePatternID FROM inserted)
END
SET NOCOUNT OFF