1、插入表触发器
CREATE OR REPLACE TRIGGER "EDITSHIP" BEFORE
INSERT ON "DTSJ" FOR EACH ROW
declare mmsi int;
shipinfommsi int;
BEGIN
select max("MMSI") into mmsi from "ShipMaterial" s left join "Material" m on s."id"=m."id"
where trim(m."name")=trim(:new.ydpth);
if mmsi is null then
select max(MMSI) into mmsi from "ShipInfo" where trim("name")=trim(:new.ydpth);
if mmsi is null then
select max(MMSI) into mmsi from "ShipInfo";
if mmsi is null then
mmsi:=1;
else
mmsi:=mmsi+1;
end if;
insert into "ShipInfo"(MMSI,"name","shipType","lat","lon","time","COG","SOG")
values( mmsi,:new.ydpth,52,:new.wd,:new.jd,:new.sj,:new.fx,:new.sd);
else
update "ShipInfo" set "shipType"=52,"lat"=:new.wd,"lon"=:new.jd,"time"=:new.sj,"COG"=:new.fx,"SOG"=:new.sd
where trim(MMSI)=trim(mmsi) and trim("name")=trim(:new.ydpth);
end if;
else
select max(MMSI) into shipinfommsi from "ShipInfo" where trim("name")=trim(:new.ydpth);
if shipinfommsi is null then
insert into "ShipInfo" (MMSI,"name","shipType","lat","lon","time","COG","SOG")
values( mmsi,:new.ydpth,52,:new.wd,:new.jd,:new.sj,:new.fx,:new.sd);
else
update "ShipInfo" set "shipType"=52,"lat"=:new.wd,"lon"=:new.jd,"time"=:new.sj,"COG"=:new.fx,"SOG"=:new.sd
where trim(MMSI)=trim(mmsi) and trim("name")=trim(:new.ydpth);
end if;
end if;
insert into "ShipHis"
(MMSI,"lat","lon","time","COG","SOG")
values( mmsi,:new.wd,:new.jd,:new.sj,:new.fx,:new.sd);
END;
2、删除触发器
CREATE OR REPLACE TRIGGER "触发器名称(delete子表名by主表ID)" BEFORE
delete ON 主表名 FOR EACH ROW
BEGIN
delete from bb where 子表中主表id=:old.主表主键id;
END;