oracle 数据库触发器的使用

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;