总结:在同一个会话不同作用域的时候(例如:大存储过程内含小存储过程的场景),使用同一个临时表名,会引起错乱
昨天在为ERP系统某个表添加了After更新触发器,添加完后,在测试库做了正向测试和逆向测试后,没什么问题。过了一会,生产部门那边就反馈,有一个环节的功能无法正常使用了,经排查是下面这个触发器的原因
DECLARE @i INT =1
DECLARE @q INT
SELECT * ,ROW_NUMBER()OVER(ORDER BY Deleted.RowPointer) AS ID INTO #Tab FROM Deleted
SET @q=(SELECT MAX(ID)FROM #Tab )
WHILE @i<=@q
BEGIN
SELECT @job=#Tab.job,@operNum=#Tab.oper_num,@site=#Tab.site_ref FROM #Tab WHERE ID=@i
SELECT @trnNum=trnnum FROM dbo.outSourceMiddle WHERE job=@job AND operNum=@operNum AND site_ref=@site
IF (ISNULL((SELECT COUNT(*) FROM dbo.WBDispatch WHERE Job=@job AND OperNum=@operNum AND site_ref=@site),0)+
ISNULL((SELECT COUNT(*) FROM dbo.jobtran_mst WHERE job=@job AND oper_num&#