请教各位关于oracle触发器的问题!

[hpym365]

请教各位关于oracle触发器的问题!


create or replace trigger tr_for_update_score
after update of ORDER_STATUS on t_bc_shopingorder
referencing old as old_value
new as new_value
for each row

when ((old_value.ORDER_STATUS=1) and (new_value.ORDER_STATUS in('2','3')) and (new_value.score=0))
declare
-- local variables here
begin
--
update t_bc_shopingorder sp --这里报错
set sp.score=1000;
--
update t_bc_customer c
set c.score=c.score+50
where c.id=(:new_value.BUYER_ID);
end tr_for_update_score;


报错地方我已标注自己感觉是因为触发器建立在这个表上然后又更新这个表造成的错误 请高手指点 谢谢

[vivianfdlpw]

更新表改为更新试图是一样的亚 晕。。。

[hpym365]

各位好心人帮帮忙啊 在线等呢。。

[vivianfdlpw]

你的触发器里边又update,构成了直接递归
可以用视图的instead of update触发器实现你的目的

create or replace view v_t_bc_shopingorder
as
select * from t_bc_shopingorder;

create or replace trigger tr_for_update_score
instead of update
on v_t_bc_shopingorder
referencing old as old_value
new as new_value
for each row
begin
update t_bc_shopingorder
set ORDER_STATUS=:new_value.ORDER_STATUS,
score=:new_value.score,
BUYER_ID=:new_value.BUYER_ID
where id=:old_value.id;

update t_bc_shopingorder sp
set sp.score=1000
where :old_value.ORDER_STATUS=1
and
sp.ORDER_STATUS in(2,3)
and
sp.score=0;
end;

[hpym365]

还是不行 不需要视图啊 我有条件限制的晕
when ((old_value.ORDER_STATUS=1) and (new_value.ORDER_STATUS in('2','3')) and (new_value.score=0))
不满足这个不让执行