有条删除触发器不会求教
create trigger [dbo].[purchase_delete]
on [dbo].[purchase]
after Delete
as
begin
delete from pcDetails where pc_id=(select purchase_id from deleted)
end
执行下面这句后,执行触发器purchase_delete:
delete from purchase purchase_id =3
但是我在表pcDetails中:
pc_id=3
的数据不只一条这样我的触发器就出问题求帮帮忙??
create trigger [dbo].[purchase_delete]
on [dbo].[purchase]
after Delete
as
begin
--delete from pcDetails where pc_id=(select purchase_id from deleted)
delete from pcDetails where pc_id IN (select purchase_id from deleted) ---->不能用等号,等号一对一没有错,但一对多时就会报错.
end
pcDetails是有另一条触发的,
create TRIGGER [dbo].[pc_delete]
ON [dbo].[pcDetails]
AFTER DELETE
AS
BEGIN
UPDATE a
SET QTY=QTY-b.qty
from store a
left join (select pc_id ,sum(qty) as qty from deleted group by pc_id )b
on a.pc_id =b.pc_id
where store.pd_id in(select pd_id from deleted)
END
create TRIGGER [dbo].[pc_delete]
ON [dbo].[pcDetails]
AFTER DELETE
AS
BEGIN
UPDATE a
SET QTY=QTY-b.qty
from store a
left join (select pd_id ,sum(qty) as qty from deleted group by pd_id )b
on a.pd_id =b.pd_id
where a.pd_id in(select pd_id from deleted) -->别名忘记改了store.pd_id改为a.pd_id
END