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;