近期写的一个比较罗嗦的Oracle after update触发器
来源:互联网 发布:iphone5s有4g网络吗 编辑:程序博客网 时间:2024/06/07 03:11
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;
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;
- 近期写的一个比较罗嗦的Oracle after update触发器
- oracle 更新后触发器(after update)
- Oracle写的触发器
- 请教一个ORACLE表的UPDATE命令如何写?
- 一个带有超级罗嗦注释的-------JS表单验证
- Oracle中Before和After触发器的例子
- 一个简单的ORACLE触发器
- 一个Oracle触发器的例子
- 触发器的简单update
- sql server触发器After Update中获取客户端IP,名称及更新前后的数据
- 罗哩罗嗦的:序
- 来自老教授的罗嗦
- 创建一个可以写本地文件的触发器
- Oracle 触发器 Update 不能操作本表的疑问
- 近期用Jquery插件写的一个自动完成页面
- 呈上一个近期用jquery写的jDialog组件
- 近期小感——一个残疾人写的操作系统
- 近期计划写的blog
- 套接字 io 模型 小结
- 学习一下
- How to read file line by line?
- sql sever自动备份
- java 读取指定行
- 近期写的一个比较罗嗦的Oracle after update触发器
- Ration Rose
- Indy10的问题
- Linux手机开发中,尽量不要用多线程。
- CSS使用记录...
- Websphere 下部署TimeManager
- 开始ACM
- 修改oracle数据库字符集的方法
- 如何寻找已注册dll的路径