oracle中我想創建trigger來實現:
在修改t_brod表的chk_id列時,判斷表中是否還有chk_id列的值為'N'的數據,如果有,將另一個表t_brom的chk_id列的值設為'N',否則設為'Y'
create or replace trigger aa
after update of chk_id on t_brod
for each row
declare
tcount integer;
begin
select count(*) into tcount from t_brod where chk_id = 'N';
if tcount = 0 then
update t_brom set chk_id = 'Y' where bro_id = :old.bro_id;
else
update t_brom set chk_id = 'N' where bro_id = :old.bro_id;
end if;
end TRI_T_BROD_INSERT;
在修改t_brod表的chk_id列時,判斷表中是否還有chk_id列的值為'N'的數據,如果有,將另一個表t_brom的chk_id列的值設為'N',否則設為'Y'
create or replace trigger aa
after update of chk_id on t_brod
for each row
declare
tcount integer;
begin
select count(*) into tcount from t_brod where chk_id = 'N';
if tcount = 0 then
update t_brom set chk_id = 'Y' where bro_id = :old.bro_id;
else
update t_brom set chk_id = 'N' where bro_id = :old.bro_id;
end if;
end TRI_T_BROD_INSERT;
請問高手這是什么原因?
after update of chk_id on t_brod
declare
tcount integer;
begin
select count(*) into tcount from t_brod where chk_id = 'N';
if tcount = 0 then
update t_brom set chk_id = 'Y' where bro_id = :old.bro_id;
else
update t_brom set chk_id = 'N' where bro_id = :old.bro_id;
end if;
end TRI_T_BROD_INSERT;
BEFORE update of chk_id on t_brod
for each row
declare
tcount integer;
begin
select count(*) into tcount from t_brod where chk_id = 'N';
if tcount = 0 AND :NEW.CHK_ID ='N' then
update t_brom set chk_id = 'Y' where bro_id = :old.bro_id;
else
update t_brom set chk_id = 'N' where bro_id = :old.bro_id;
end if;
end TRI_T_BROD_INSERT;
如果不加则为语句级触发器,语句执行一次,触发一次。
应该是这句where bro_id = :old.bro_id;
有问题。