觸發器的應用（商店打折）

--用戶信息表
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_vip
after
update of grade
on useres
for each row
when (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;