August 19, 2009 at 12:48 pm
I have an insert trigger that will always skip the first row of the inserted table. There is a function in the trigger dbo.fnParseString that is taking a varchar field(123456-111111) and splitting it in into individual records. so now there is a record for 123456 and 111111 and associating it with another field. For some reason what ever the first Id is it is always missing from the new table. I know this sounds confusing so if you need more info please let me know. My trigger is below.
create TRIGGER dbo.TR_BUY_Insert_ProductChoiceItemProductInventory
ON dbo.bvc_ProductInventory
AFTER Insert
NOT FOR Replication
/*
USAGE:
Procedure Name: TR_BUY_Insert_ProductChoiceItemProductInventoryoryKey
Created By: Pam Ozer
Create Date: August 18, 2009
RevisionNo: 0
Modified By:
CREATE PURPOSE: Inserts the dbo.Buy_ProductChoiceItemProductInventory table with the new Choice Item IDs based on the InventoryKey
MODIFICATION Details:
*/
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
Declare @Inventorykey Nvarchar(50)
Set @inventorykey=(Select Inventorykey from Inserted)
If @inventorykey='0'
Return
BEGIN TRANSACTION
INSERT INTO dbo.Buy_ProductInventoryProductChoiceItem
( ProductInventoryID, ProductChoiceItemID )
SELECT .ID, dbo.fnparsestring(1,'-',I.inventorykey)
FROM Inserted (nolock)
where dbo.fnparsestring(1,'-',I.inventorykey) is not null
Union all
SELECT .ID, dbo.fnparsestring(2,'-',I.inventorykey)
FROM Inserted (nolock)
where dbo.fnparsestring(2,'-',I.inventorykey) is not null
Union all
SELECT .ID, dbo.fnparsestring(3,'-',I.inventorykey)
FROM Inserted (nolock)
where dbo.fnparsestring(3,'-',I.inventorykey) is not null
Union all
SELECT .ID, dbo.fnparsestring(4,'-',I.inventorykey)
FROM Inserted (nolock)
where dbo.fnparsestring(4,'-',I.inventorykey) is not null
Union all
SELECT .ID, dbo.fnparsestring(5,'-',I.inventorykey)
FROM Inserted (nolock)
where dbo.fnparsestring(5,'-',I.inventorykey) is not null
COMMIT TRANSACTION
--RETURN 0
END TRY
BEGIN CATCH
IF Xact_State() = -1
ROLLBACK TRANSACTION
IF Xact_State() = 1
COMMIT TRANSACTION
--RETURN @@Error
END CATCH
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT OFF
GO
GO
August 19, 2009 at 12:59 pm
Hi,
I don't see anything odd in your code, can you provide the source code for this function : dbo.fnparsestring().
Maybe you need to send the parameter (0) to it, to get the first number? Just a guess.
Cheers,
J-F
August 19, 2009 at 1:28 pm
While waiting on the parsestring function to be posted, I will also ask if you have considered rewriting it as a parse to produce a derived table to feed the INSERT, instead of using a bunch of UNION ALL queries.
declare @sampleIn varchar(8000)
declare @sampleOut table (data char(5))
set @sampleIn = '12345-67890-23456-78901-34567'
set @sampleIn = '-'+@sampleIn+'-'
;with tally (N) as (select ROW_NUMBER() over(order by id) from master..syscolumns)
,parseCTE AS
(select substring(@sampleIn,N+1,charindex('-',@sampleIn,N+1)-(N+1)) as element
from tally
where substring(@sampleIn,N,1) = '-'
and N < len(@sampleIn)
)
insert into @sampleOut
select * from parseCTE
select * from @sampleOut
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 19, 2009 at 2:01 pm
Here is a copy of the function. I did try using a 0 but that didn't work. It is looking at the sections before and after the - so that's why that wouldn't work. And No I hadn't thought of the derived table. I will have to see if that will work. Thanks for all your help. Any other help would be greatly appreciated
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnParseString]
(
@Section SMALLINT,
@Delimiter CHAR,
@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE
@NextPos SMALLINT,
@LastPos SMALLINT,
@Found SMALLINT
SELECT
@NextPos = 0,
@Found = 0
IF @Section > 0
SELECT @Text = REVERSE(@Text)
WHILE @NextPos <= DATALENGTH(@Text) AND @Found < ABS(@Section)
SELECT @LastPos = @NextPos,
@NextPos = CASE
WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
END,
@Found = @Found + 1
IF @Found ABS(@Section) OR @Found = 0 OR @Section = 0
SELECT @Text = NULL
ELSE
SELECT @Text = SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
RETURN CASE WHEN @Section < 0 THEN @Text ELSE REVERSE(@Text) END
END
August 19, 2009 at 2:11 pm
Still don't see why what you have isn't working. But I do see that you are using a while loop in your parse function. Take a look in SSC at the various threads and articles about parsing using a tally table instead of a while loop. You stand to gain a bit of performance just rewriting that function.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply