这样的触发器该如何做啊?
有两个表:person(per_no,qty),stock(per_no,qty)
我想对person表的操作都自动更新stock表的qty数值。
1)在person表新增时,则stock表对应的per_no号的qty值为
stock.qty=stock.qty+person.qty
2)在person表删除时,则stock表对应的person.per_no=stock.per_no的
stock.qty=stock.qty+person.qty
2)在person表更新时,则stock表对应的person.per_no=stock.per_no的
stock.qty=stock.qty-person.qty旧值+person.qty新值
有两个表:person(per_no,qty),stock(per_no,qty)
我想对person表的操作都自动更新stock表的qty数值。
1)在person表新增时,则stock表对应的per_no号的qty值为
stock.qty=stock.qty+person.qty
2)在person表删除时,则stock表对应的person.per_no=stock.per_no的
stock.qty=stock.qty+person.qty
2)在person表更新时,则stock表对应的person.per_no=stock.per_no的
stock.qty=stock.qty-person.qty旧值+person.qty新值
after insert or update or delete on person
for each row
begin
if inserting then
update stock set qty=qty+:new.qty where per_son=:new.per_son;
end;
if deleting then
update stock set qty=qty-:old.qty where per_son=:old.per_son;
end;
if updating then
update stock set qty=qty-:old.qty+:new.qty where per_son=:new.per_son;
end;
end;
after insert or update or delete on person
for each row
begin
if inserting then
update stock set qty=qty+:new.qty where per_no=:new.per_no;
elsif deleting then
update stock set qty=qty-:old.qty where per_no=:old.per_no;
else
update stock set qty=qty-:old.qty+:new.qty where per_no=:new.per_no;
end if;
end;
create or replace trigger tri_person before insert or delete or update on person
for each row
as
begin
if inserting then
update stock set stock.qty=stock.qty+:new.person.qty
where per_no=:new.per_no
elsif deleting then
update stock set stock.qty=stock.qty+:old.person.qty
where per_no=:old.per_no
else
update stock set stock.qty=stock.qty-:old.person.qty+:new.person.qty
where per_no=:new.per_no
end if;
commit;
end;[/code]
--多写了as
create or replace trigger tri_person before insert or delete or update on person
for each row
begin
if inserting then
update stock set stock.qty=stock.qty+:new.person.qty
where per_no=:new.per_no
elsif deleting then
update stock set stock.qty=stock.qty+:old.person.qty
where per_no=:old.per_no
else
update stock set stock.qty=stock.qty-:old.person.qty+:new.person.qty
where per_no=:new.per_no
end if;
commit;
end;