数据调整代码直接执行时运行良好 - 但在触发器中使用时永远不会停止
我编写了一些代码来确保订单上的商品都已编号（“位置编号”或“商品编号”最近才引入，我们不想去更改所有相关代码 - 因为它是“仅美观”并且没有功能影响。）
因此，我们的想法是检查包含 NULL 或 0 的 itemno 的记录 - 然后计算 1 并分配它。在查询窗口中执行此代码时，它工作正常。当将其放入 AFTER INSERT 触发器中时，它会永远循环。
那么这里出了什么问题呢？
/****** 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
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Aaaargh - 刚刚发现问题：当 WHERE 的结果为 NULL 时，第 3d 行（从末尾开始，SELECT 语句）不会分配变量。因此，在该语句之前“set @idp=null”就解决了问题！
不确定为什么我假设直接执行和触发这些语句之间存在问题，似乎我选择了错误的测试用例:(
Aaaargh - just found the problem: the 3d line (from the end, the SELECT-statement) does not assign the variables when there result of the WHERE is NULL. So "set @idp=null" before that statement has fixed the problem!
Not sure whhy I assumed a problem between direct execution and triggering of these statements, seems I picked the wrong test-cases :(