触发器的应用（商店打折）

--用户信息表create table useres( u_id number(13,0) primary key, username varchar2(50) not null, password varchar2(20) not null, name varchar2(50), sex char(4), birthday date, phonenum varchar2(11), grade int default 0 --积分);

--会员表create table vip( vid varchar2(20) primary key, u_id number(13,0), discount number(3,2), --折扣 constraint fk_vip foreign key(u_id) references useres(u_id));

insert into useres values(2016061600001,'jacky','123456','张三','男','23-7月-88','13548643025',default);insert into useres values(2016061600002,'mary','mary','王红','女','20-7月-98','13748643025',default);insert into useres values(2016061600003,'jason','123456','李四','男','23-7月-88','13948743025',default);commit;

--触发器create or replace trigger trigger_vipafterupdate of gradeon useresfor each rowwhen (new.grade >= 10000)declare vcount int; v_vid varchar2(20) := 'V' || to_char(sysdate,'yyyyMMdd') || '00001'; v_discount number(3,2);begin select count(*) into vcount from vip; if vcount > 0 then v_vid := substr(v_vid,0,length(v_vid) - 5) || lpad(to_char((to_number(substr(v_vid,length(v_vid) - 4,5),'99999') + 1)),5,0); end if; case when :new.grade >= 10000 and :new.grade <= 50000 then v_discount := 0.95; when :new.grade >= 60000 and :new.grade <= 100000 then v_discount := 0.90; when :new.grade >= 100000 then v_discount := 0.80; end case;

select count(*) into vcount from vip where u_id = :old.u_id; if vcount > 0 then select vid into v_vid from vip where u_id = :old.u_id; update vip set discount = v_discount where vid = v_vid; else insert into vip values(v_vid,:old.u_id,v_discount); end if;end;

--测试update useres set grade = grade + 50000 where u_id = 2016061600001;commit;

 

转载于:https://www.cnblogs.com/hgc-bky/p/5592073.html

相关资源：JAVA上百实例源码以及开源项目