关于update的触发器,高手帮忙啊
我的目的是更新一个叶子结点的数量时,及时更新此结点所有父结点的价格,但是没有达到要求,不会更新,请各位大哥帮小弟看看,先谢过了
ALTER TRIGGER updatepricebynum
ON dbo.RealRationanalyse
FOR UPDATE
AS
if update(num)
begin
declare @num float(8)
declare @num1 float(8)
declare @price money
declare @balance float(8)
declare @marketprice money
declare @pid int
declare @parentid int
select @num=realrationanalyse.num from realrationanalyse,deleted where realrationanalyse.id=deleted.id
select @num1=inserted.num,@price=realrationanalyse.marketprice,@pid=realrationanalyse.parentid from realrationanalyse,inserted where realrationanalyse.id=inserted.id
set @balance=@num1-@num
set @balance=@balance*@price
select @marketprice=marketprice,@num=num from realrationanalyse where id=@pid
set @marketprice=@marketprice+@balance
--更新其父结点的市场价格
update realrationanalyse set marketprice=@marketprice where id=@pid
set @balance=@balance*@num
--一直往上查找其父结点,直到找到根结点为止
select @parentid=parentid from realrationanalyse where id=@pid
while(@parentid<>0)
begin
set @pid=@parentid
select @marketprice=marketprice,@parentid=parentid,@num=num from realrationanalyse where id=@parentid
update realrationanalyse set marketprice=@marketprice+@balance where id=@pid
set @balance=@balance*@num
end
end