自己写的第一个triger

bibiye

浏览: 171169 次

最近访客更多访客>>

xiaobojava

Janle

Dreamall

zenghong237

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

DB相关

SQL

简化前：

sql 代码

Create Or Replace Trigger tr_cust_order
AFTER UPDATE
ON te_cust_order
FOR EACH ROW
WHEN (NEW.status=6)
DECLARE
exhok NUMBER;
exhid NUMBER;
regok NUMBER;
seqexh NUMBER;
sql_stat varchar2(200);
BEGIN
--判断是否为展览订单
DECLARE
CURSOR emp_cursor IS
SELECT service_item_id FROM te_cust_order
WHERE service_item_id IN
(SELECT t.id FROM te_cust_service_item t WHERE t.service_type_id IN (2,3,4) );
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
exit when emp_cursor%NOTFOUND;
if emp_record.service_item_id = :old.SERVICE_ITEM_ID then
exhok := 1;
end if;
END LOOP;
CLOSE emp_cursor;
END;
if exhok != 1 then
return;
end if;
--取得展览ID
exhid := 0;
DECLARE
CURSOR exh_cursor IS
SELECT exh_id FROM te_exh_order
WHERE order_no = :new.order_no;
exh_record exh_cursor%ROWTYPE;
BEGIN
OPEN exh_cursor;
LOOP
FETCH exh_cursor INTO exh_record;
exit when exh_cursor%NOTFOUND;
exhid := exh_record.exh_id ;
END LOOP;
CLOSE exh_cursor;
END;
if exhid < 1 then
return;
end if;
--判断是否已经报名了
regok := 0;
SELECT count(*) INTO regok FROM teexhibitiontomember
WHERE EXHIBITIONID = exhid and CUSTOMERNO = :new.CUSTOMER_NO;
if regok >0 then
return;
end if;
--增加报名记录
select teexhibitionmemberseq.nextval into seqexh
from teexhibitiontomember t where rownum = 1;
sql_stat := 'insert into teexhibitiontomember(ID,MEMBERID,EXHIBITIONID,CUSTOMERNO) values ('
|| seqexh || ',0,' ||exhid||','''|| :new.CUSTOMER_NO||''')';
execute immediate sql_stat;
END tr_cust_order;

简化后：

sql 代码

/*==============================================================*/
/* Trigger： tr_cust_order */
/*==============================================================*/
Create Or Replace Trigger tr_cust_order
AFTER UPDATE ON te_cust_order
FOR EACH ROW
WHEN (NEW.status = 6)
DECLARE
exhid NUMBER;
regok NUMBER;
seqexh NUMBER;
sql_stat varchar2(200);
BEGIN
--取得展览ID
SELECT exh_id
into exhid
FROM te_exh_order
WHERE order_no = :new.order_no;
if exhid < 1 then
return;
end if;
--判断是否已经报名了
regok := 0;
SELECT count(*)
INTO regok
FROM teexhibitiontomember
WHERE EXHIBITIONID = exhid
and CUSTOMERNO = :new.CUSTOMER_NO;
if regok > 0 then
return;
end if;
--增加报名记录
select teexhibitionmemberseq.nextval into seqexh from dual;
sql_stat := 'insert into teexhibitiontomember(ID,MEMBERID,EXHIBITIONID,CUSTOMERNO) values (' ||
seqexh || ',0,' || exhid || ',''' || :new.CUSTOMER_NO ||
''')';
execute immediate sql_stat;
END tr_cust_order;

分享到：

有史以来最伟大的500首歌曲 | 转：健康

2007-02-13 19:02
浏览 1368
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

自己写的第一个triger

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

自己写的第一个triger

评论

发表评论

相关推荐

[oracle] 用户管理

[Oracle] 表空间

查看和清除数据库锁对象

最近访客更多访客>>