Data tweaking code runs fine when executed directly - but never stops when used in trigger
- by MBaas
I have written some code to ensure that items on an order are all numbered (the "position number" or "item number" has been introduced only recently and we did not want to go and change all related code - as it is "asthetics only" and has no functional impact.)
So, the idea is to go and check for an records that jave an itemno of NULL or 0 - and then compute one and assign it. When executing this code in a query window, it works fine. When putting it into an AFTER INSERT-trigger, it loops forever.
So what is wrong here?
/****** Objekt: Trigger [SetzePosNr] Skriptdatum: 02/28/2010 20:06:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [SetzePosNr]
ON [dbo].[bestellpos]
AFTER INSERT
AS
BEGIN
DECLARE @idb int
DECLARE @idp int
DECLARE @pnr int
SELECT @idp=id,@idb=id_bestellungen FROM bestellpos WHERE posnr IS NULL OR posnr=0
WHILE @idp IS NOT NULL
BEGIN
SELECT @pnr = 1+max(posnr) FROM bestellpos WHERE id_bestellungen = @idb
print( 'idp=' + str(@idp) + ', idb=' + str(@idb) + ', posnr=' + str(@pnr))
UPDATE bestellpos SET posnr=@pnr WHERE id=@idp
SELECT @idp=id,@idb=id_bestellungen FROM bestellpos WHERE posnr IS NULL OR posnr=0
END
END
