CREATE TRIGGER onPlanMo1Delete ON [dbo].[PlanMO1]
FOR DELETE
AS
declare @MoNo varchar(20),
@line smallint;
SELECT @MoNo=MoNo,@line=Line FROM DELETED --这里有问题,这一句只会取deleted 表的第一条记录,当删除多条记录时,你后面的记录就无法做判断了.
IF (SELECT PKQty+PKQtyL FROM PlanMO2 WHERE PMONO=@MoNo AND PMoLine=@line)>0
Begin
rollback transaction
endIF (SELECT PKQty+PKQtyL FROM PlanMO2 WHERE PMONO=@MoNo AND PMoLine=@line)=0
Begin
DELETE PlanMO2 from PlanMo2 join deleted on PlanMO2.PMONO =deleted.MoNo AND PlanMo2.PMoLine=deleted.Line
end
FOR DELETE
AS
declare @MoNo varchar(20),
@line smallint;
SELECT @MoNo=MoNo,@line=Line FROM DELETED --这里有问题,这一句只会取deleted 表的第一条记录,当删除多条记录时,你后面的记录就无法做判断了.
IF (SELECT PKQty+PKQtyL FROM PlanMO2 WHERE PMONO=@MoNo AND PMoLine=@line)>0
Begin
rollback transaction
endIF (SELECT PKQty+PKQtyL FROM PlanMO2 WHERE PMONO=@MoNo AND PMoLine=@line)=0
Begin
DELETE PlanMO2 from PlanMo2 join deleted on PlanMO2.PMONO =deleted.MoNo AND PlanMo2.PMoLine=deleted.Line
end
CREATE TRIGGER onPlanMo1Delete ON [dbo].[PlanMO1]
FOR DELETE
AS
declare @MoNo varchar(20),
@line smallint;
declare #aa cursor for select mono,line from deleted
open #aa
fetch next from #aa into @mono,@line
while @@fetch_status=0
begin
IF (SELECT PKQty+PKQtyL FROM PlanMO2 WHERE PMONO=@MoNo AND PMoLine=@line)>0
Begin
rollback transaction
return
end IF (SELECT PKQty+PKQtyL FROM PlanMO2 WHERE PMONO=@MoNo AND PMoLine=@line)=0
Begin
DELETE from PlanMo2 where PMONO =@MoNo AND PMoLine=@Line
end
fetch next from #aa into @mono,@line
end
close #aa
deallocate #aa
CREATE TRIGGER onPlanMo1Delete ON [dbo].[PlanMO1]
FOR DELETE
AS
DELETE PlanMO2 from PlanMo2 a inner join deleted b on a.PMONO =b.MoNo AND a.PMoLine=b.Line
--这句有问题的,如果返回值多于一个,当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
CREATE TRIGGER onPlanMo1Delete ON [dbo].[PlanMO1]
FOR DELETE
ASIF (SELECT sum(PKQty+PKQtyL) FROM PlanMO2 a innner join deleted b on a.PMONO =b.MoNo AND a.PMoLine=b.Line)=0 --仅为0时删除,当然PKQty+PKQtyL不可能有负数存在吧?如果有的话,就需要另行处理了.
DELETE PlanMO2 from PlanMo2 join deleted on PlanMO2.PMONO =deleted.MoNo AND PlanMo2.PMoLine=deleted.Line
else
rollback tran你解释一下:PKQty,PKQtyL的含义,它们的值为正,还是为负,还是两者均可,对数据处理有何影响?
PKQty,PKQtyL是我想了很久的一个办法,我解释一下吧,因为生产的产品分左右边成付,也有单支的,还有生产误差得出的只有左边或右边(也就是单边的)
为了区分这些所以做了二个字段表示一个产品左右边的数量!!
因为更新只有一条语句,所以不用begin...end
如果真的的报错,那没办法,你还是加上begin...end吧.
如果你的连else语句都不认,就这样修改吧:
CREATE TRIGGER onPlanMo1Delete ON [dbo].[PlanMO1]
FOR DELETE
AS
declare @aa int --这个数据类型根据PKQty+PKQtyL的数据类型来定义
SELECT @aa=sum(PKQty+PKQtyL) FROM PlanMO2 a innner join deleted b on a.PMONO =b.MoNo AND a.PMoLine=b.LineIF @aa=0
begin
DELETE PlanMO2 from PlanMo2 join deleted on PlanMO2.PMONO =deleted.MoNo AND PlanMo2.PMoLine=deleted.Line
end
if @aa>0
begin
rollback tran
end
是判断存在记录么
那用
if exists (SELECT PKQty+PKQtyL FROM PlanMO2 WHERE PMONO=@MoNo AND PMoLine=@line)