用SQL SERVER很容易实现了。但ORACLE还不是很熟悉。我是这样写的: create or replace trigger AU_I_UNIT_IS_HAVE_ACC after insert or delete on au_i_bank for each row declare rscount int; begin if INSERTING THEN update user set IS_HAVE_CAR='yes' where id=:new.id; end if;
if DELETING THEN select count(*) into rscount from car where id=:old.id; if rscount =0 then update user set IS_HAVE_CAR='no' where id=:old.id; end if; end if;
end AU_I_UNIT_IS_HAVE_ACC; 这样INSERTING可以了,但DELETING报错该如何修改?大家能否给出个另外的思路?
create or replace trigger trg_test after insert or delete on car for each row begin if inserting then update user set is_have_car='yes' where id=:new.id; else if deleting then update user set is_have_car='no' where id=:old.id; end if; end trg_test;
TO more_zou(小人物) ( ): 删除的时候,不判断表CAR是否还存在此ID的数据了吗?
同意小人物的 to lastsky 不用
select count(*) into rscount from car where id=:old.id; if rscount =0 then update user set IS_HAVE_CAR='no' where id=:old.id; 没法这样判断,因为触发器触发时表正在发生变化,这样查询会出错
不明楼主,存在二条语句也是这样做法,有何不对? create or replace trigger trg_test after insert or delete on car for each row begin if inserting then update user set is_have_car='yes' where id=:new.id; else update user set is_have_car='no' where id=:old.id; end if; exception when others then null; end trg_test;
create or replace trigger AU_I_UNIT_IS_HAVE_ACC
after insert or delete on au_i_bank
for each row
declare
rscount int;
begin
if INSERTING THEN
update user set IS_HAVE_CAR='yes' where id=:new.id;
end if;
if DELETING THEN
select count(*) into rscount from car where id=:old.id;
if rscount =0 then
update user set IS_HAVE_CAR='no' where id=:old.id;
end if;
end if;
end AU_I_UNIT_IS_HAVE_ACC;
这样INSERTING可以了,但DELETING报错该如何修改?大家能否给出个另外的思路?
after insert or delete on car for each row
begin
if inserting then
update user set is_have_car='yes' where id=:new.id;
else if deleting then
update user set is_have_car='no' where id=:old.id;
end if;
end trg_test;
删除的时候,不判断表CAR是否还存在此ID的数据了吗?
to lastsky 不用
if rscount =0 then
update user set IS_HAVE_CAR='no' where id=:old.id;
没法这样判断,因为触发器触发时表正在发生变化,这样查询会出错
http://expert.csdn.net/Expert/topic/1905/1905908.xml?temp=6.950015E-02
create or replace trigger trg_test
after insert or delete on car
for each row
begin
if inserting then
update user set is_have_car='yes' where id=:new.id;
else
update user set is_have_car='no' where id=:old.id;
end if;
exception
when others then
null;
end trg_test;