我写的一个触发器的小例子,已经测试通过
表A和A_active结构相似都有如下字段
id name land_no
create or replace trigger t_A
before insert or update or delete on A
for each row
declare
pbCount number;
begin
select count(land_no) into pbCount from A_active where land_no = :new.land_no;
if inserting or updating then
if pbCount<=0 then
insert into A_active (id,name,land_no) values (:new.id,:new.name,:new.land_no);
else
update A_active
set id = :new.id,
name = :new.name ,
where land_no = :new.land_no;
end if;
end if;
if deleting then
delete from A_active where land_no = :old.land_no;
end if;
end;
功能就是A表插入数据时 ,在A_active中查找,如果存在则更新,不存在则插入。
当A 表数据更新时,A_active也更新
当A表删除记录时,A_active也将相应记录删除