Application of triggers (store discount)

--User Information table
CREATE TABLE Useres
(
u_id Number (13,0) primary key,
Username VARCHAR2 () not NULL,
Password varchar2 () not NULL,
Name VARCHAR2 (50),
Sex char (4),
Birthday date,
Phonenum VARCHAR2 (11),
Grade int default 0--integral
);

--Membership Form
CREATE TABLE VIP
(
Vid varchar2 (primary key),
u_id number (13,0),
Discount number (3,2),--discount

Constraint FK_VIP foreign KEY (u_id) references Useres (u_id)
);

INSERT into useres values (2016061600001, ' Jacky ', ' 123456 ', ' Zhang San ', ' Male ', ' 2 March-July -88 ', ' 13548643025 ', default);
INSERT into useres values (2016061600002, ' Mary ', ' Mary ', ' Wang Hong ', ' female ', ' 20月-July -98 ', ' 13748643025 ', default);
INSERT into useres values (2016061600003, ' Jason ', ' 123456 ', ' John Doe ', ' Male ', ' 2 March-July -88 ', ' 13948743025 ', default);
Commit

--Trigger
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: = ' 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

--Test
Update Useres Set grade = Grade + 50000 where u_id = 2016061600001;
Commit

Application of triggers (store discount)