背景
触发器1:请购单删除时候连带删除
create Trigger [dbo].[PU_AppVouchs_delete]
/*
请购删除,同步清空排程的下达情况
*/
on [dbo].[PU_AppVouchs]
for delete
as
SELECT AutoID INTO #temp FROM Deleted
--SELECT * FROM dbo.Z3_MOThirdSchedure
UPDATE dbo.Z3_MOFirstSchedure SET cToNextTbl = NULL ,iToNextID = NULL, cToNextCode = NULL WHERE cToNextTbl = 'PU_AppVouchs' AND iToNextID IN (SELECT AutoID FROM #temp)
--这一句导致错误
UPDATE dbo.Z3_MOSecondSchedure SET cToNextTbl = NULL ,iToNextID = NULL, cToNextCode = NULL WHERE cToNextTbl = 'PU_AppVouchs' AND iToNextID IN (SELECT AutoID FROM #temp)
select cToNextTbl, iToNextID,cToNextCode, * from Z3_MOSecondSchedure
UPDATE dbo.Z3_MOThirdSchedure SET cToNextTbl = NULL ,iToNextID = NULL, cToNextCode = NULL WHERE cToNextTbl = 'PU_AppVouchs' AND iToNextID IN (SELECT AutoID FROM #temp)
DROP TABLE #temp
触发器2:排程表对应修改生产订单。
CREATE TRIGGER tri_SecondSchedure
ON z3_mosecondschedure
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
if UPDATE(cFree1)
begin
-- --修改A对应的生产订单子件
select b.iToNextID, a.cinvcode,a.iqty,a.izhishu ,a.cFree1 into #temp from inserted a
inner join Z3_MOFirstSchedure b on a.cVouchCode = b.cVouchCode and b.cinvcode + '|1|' + b.cFree1 = a.cFree10 and isnull(b. iToNextID,0)<> 0
update mom_moallocate set BaseQtyN = k.cFree1, Free1 = k.cFree1 ,qty = k.iqty from #temp k where mom_moallocate.MoDId = k.iToNextID and mom_moallocate.InvCode = k.cinvcode
drop table #temp
end
END
GO
问题:当删除第一个表时候,报错触发器 tri_SecondSchedure 中 itoNextID,cinvcode 字段不存在。
排查:百思不得其解,对了下字段没问题。
灵光乍现: 发现两个触发器都用到了临时表,且临时表名都一样,所以SQL肯定认为第二个触发器里面的#temp 与第一个触发器里面的#temp是同一回事。
解决方案:将第二个临时表修改为#tempMoSecondSchedure 即可。
记忆点:应该避免临时表名称都一致,导致意外现象。数据库级别的临时表在一个会话部分是不隔离的。