CREATE TRIGGER sf_dispatchlists ON [dbo].[DispatchLists] FOR INSERT,UPDATE as update DispatchLists set iunitprice=iinvncost, itaxunitprice=iinvncost+iinvncost*17/100, imoney=round(iinvncost*Inserted.iquantity,2), itax=round(iinvncost*Inserted.iquantity*17/100,2), isum=round(iinvncost*Inserted.iquantity+iinvncost*Inserted.iquantity*17/100,2), inatunitprice=iinvncost*iexchrate, inatmoney=round(iinvncost*iexchrate*Inserted.iquantity,2), inattax=round(iinvncost*iexchrate*Inserted.iquantity*17/100,2), inatsum=round(iinvncost*iexchrate*Inserted.iquantity+iinvncost*iexchrate*Inserted.iquantity*17/100,2) from inserted inner join inventory on inserted.cinvcode=inventory.cinvcode --inner join dispatchlist on inserted.dlid=dispatchlist.dlid where inserted.dlid=dispatchlist.dlid alter table [dbo].[DispatchLists] disable trigger [sf_dispatchlists]
也就是说把你的DispatchLists表和新插入的记录所在的虚拟表inserted建立关系。
这句inner join dispatchlist on inserted.dlid=dispatchlist.dlid 不能去掉的,我用到了dispatchlist表中的字段iexchrate
我是这样改的:CREATE TRIGGER sf_dispatchlists ON [dbo].[DispatchLists] FOR INSERT,UPDATE asupdate DispatchLists set iunitprice=iinvncost, itaxunitprice=iinvncost+iinvncost*17/100, imoney=round(iinvncost*Inserted.iquantity,2), itax=round(iinvncost*Inserted.iquantity*17/100,2), isum=round(iinvncost*Inserted.iquantity+iinvncost*Inserted.iquantity*17/100,2), inatunitprice=iinvncost*iexchrate, inatmoney=round(iinvncost*iexchrate*Inserted.iquantity,2), inattax=round(iinvncost*iexchrate*Inserted.iquantity*17/100,2), inatsum=round(iinvncost*iexchrate*Inserted.iquantity+iinvncost*iexchrate*Inserted.iquantity*17/100,2) from inserted inner join inventory on inserted.cinvcode=inventory.cinvcode inner join dispatchlist on inserted.dlid=dispatchlist.dlid where dispatchlists.dlid=inserted.dlid
FOR INSERT,UPDATE
as
update DispatchLists
set iunitprice=iinvncost,
itaxunitprice=iinvncost+iinvncost*17/100,
imoney=round(iinvncost*Inserted.iquantity,2),
itax=round(iinvncost*Inserted.iquantity*17/100,2),
isum=round(iinvncost*Inserted.iquantity+iinvncost*Inserted.iquantity*17/100,2),
inatunitprice=iinvncost*iexchrate,
inatmoney=round(iinvncost*iexchrate*Inserted.iquantity,2),
inattax=round(iinvncost*iexchrate*Inserted.iquantity*17/100,2),
inatsum=round(iinvncost*iexchrate*Inserted.iquantity+iinvncost*iexchrate*Inserted.iquantity*17/100,2)
from inserted
inner join inventory on inserted.cinvcode=inventory.cinvcode
--inner join dispatchlist on inserted.dlid=dispatchlist.dlid
where inserted.dlid=dispatchlist.dlid
alter table [dbo].[DispatchLists] disable trigger [sf_dispatchlists]
FOR INSERT,UPDATE
asupdate DispatchLists
set iunitprice=iinvncost,
itaxunitprice=iinvncost+iinvncost*17/100,
imoney=round(iinvncost*Inserted.iquantity,2),
itax=round(iinvncost*Inserted.iquantity*17/100,2),
isum=round(iinvncost*Inserted.iquantity+iinvncost*Inserted.iquantity*17/100,2),
inatunitprice=iinvncost*iexchrate,
inatmoney=round(iinvncost*iexchrate*Inserted.iquantity,2),
inattax=round(iinvncost*iexchrate*Inserted.iquantity*17/100,2),
inatsum=round(iinvncost*iexchrate*Inserted.iquantity+iinvncost*iexchrate*Inserted.iquantity*17/100,2)
from inserted inner join inventory
on inserted.cinvcode=inventory.cinvcode
inner join dispatchlist on inserted.dlid=dispatchlist.dlid
where dispatchlists.dlid=inserted.dlid