近期写的一个比较罗嗦的Oracle after update触发器

CREATE OR REPLACE TRIGGER TRG_PRODUCTLIST_UPDATE
AFTER UPDATE OF ISPUBLISH
ON PRODUCTLIST
FOR EACH ROW
declare
ProvideNo ProvideInfo.ProvideNo%type;
InfoTitle ProvideInfo.InfoTitle%type;
FarmProduct ProvideInfo.FarmProduct%type;
FarmClass ProvideInfo.FarmClass%type;
Amount ProvideInfo.Amount%type;
Price ProvideInfo.Price%type;
ProductImage Provideinfo.Productimage%type;
Info Provideinfo.Info%type;
Contact Provideinfo.Contact%type;
RegionNo Provideinfo.Regionno%type;
Region1 Provideinfo.Region1%type; --地市
Region2 Provideinfo.Region2%type; --区县
MainRegionNo Region.Pregionno%type; --地市编号
PublicTime Provideinfo.Publictime%type;
PublicStatus Provideinfo.Publicstatus%type;
IsLimitDays Provideinfo.Islimitdays%type;
InfoPri Provideinfo.Infopri%type;
FromMode Provideinfo.Frommode%type;
FromMobile Provideinfo.Frommobile%type;
FromUserNo Provideinfo.Fromuserno%type;
FromUserName Provideinfo.Fromusername%type;
SubmitTime Provideinfo.Submittime%type;
begin
if :New.ISPUBLISH=0 then
delete from ProvideInfo where ProvideNo=:NEW.ProvideNo;
delete from Productrecommend where ProvideNo=:NEW.ProvideNo;
end if;

if :New.ISPUBLISH=1 then
select getNext_ProvideNo into ProvideNo from dual;
InfoTitle:='供应'+:NEW.FarmProduct;
FarmProduct:=:NEW.FarmProduct;
FarmClass:=:NEW.FarmClass;
Amount:=:NEW.Amount;
Price:=:NEW.Price;
ProductImage:=:NEW.ProductImage;
Info:=:NEW.Info;
select SpaceName+ContactTel into Contact from SpaceList where SpaceNo=:new.SpaceNo;
select RegionNo,SpacePri,SpaceNo,SpaceName
into RegionNo,InfoPri,FromUserNo,FromUserName
from SpaceList where SpaceNo=:new.SpaceNo;
--取区县
select Region,PRegionNo into Region2,MainRegionNo from Region where Region.Regionno=Regionno;
--取地市
select Region into Region1 from region where PRegionNo=MainRegionNo;
select sysdate into PublicTime from dual;
PublicStatus:=1;
IsLimitDays:=0;
FromMode:=7;
select mobile into FromMobile from users where SpaceNo=:new.SpaceNo;
select sysdate into SubmitTime from dual;

insert into ProvideInfo(ProvideNo,InfoTitle,FarmProduct,FarmClass,Amount,
Price,ProductImage,Info,Contact,RegionNo,Region1,Region2,PublicTime,PublicStatus,
IsLimitDays,InfoPri,FromMode,FromMobile,FromUserNo,FromUserName,SubmitTime)
values(ProvideNo,InfoTitle,FarmProduct,FarmClass,Amount,
Price,ProductImage,Info,Contact,RegionNo,Region1,Region2,PublicTime,PublicStatus,
IsLimitDays,InfoPri,FromMode,FromMobile,FromUserNo,FromUserName,SubmitTime);
end if;
end TRG_PRODUCTLIST_UPDATE;