实验二、编写ORALCE触发器与过程
一、在stud_417表和class_417表中加入一列sum_evaluation
alter table stud_417 add (sum_evaluation number default 100);
alter table class_417 add(sum_evaluation number default 100);
commit;
二、建立上课考勤总表Attend_417
create table Attend_417
(
riqi date,
unit char,
sno varchar2(10) references stud_417(sno),
cno varchar2(10) references class_417(cno),
condition varchar2(10),
primary key(riqi,unit,sno)
);
alter table attend_417 add constraint ck_attend_con check2016/5/7
(contidion in ('正常','迟到','请假','旷课'));
alter table attend_417 add constraint ck_attend_unit check
(unit in ('1','2','3','4','5'));
三、建立个人考勤汇总表stud_attend和班级考勤汇总表class_attend。
1.个人考勤汇总表stud_attend
create table stud_attend
(
sno varchar2(10) primary key,
beginDate date,
endDate date,
zhengChangCiShu number,
chiDaoCiShu number,
kuangKeCiShu number,
qingJiaCiShu number,
koufen number
);
2.班级考勤汇总表class_attend
create table class_attend
(
cno varchar2(10) primary key,
beginDate date,
endDate date,
zhengChangCiShu number,
chiDaoCiShu number,
kuangKeCiShu number,
qingJiaCiShu number,
koufen number
);
/*修改主键 为cno,beginDate,endDate*/
三、建立触发器,当对考勤表Attend表进行相应插入、删除、修改时,对stud表的sum_evaluation 数值进行相应的数据更新。
create or replace trigger attend_stud
before insert or update or delete on Attend_417
for each row
begin
if inserting then
if :new.condition = '迟到' then
update stud_417 set sum_evaluation=sum_evaluation-2 where :new.sno = stud_417.sno;
一、在stud_417表和class_417表中加入一列sum_evaluation
alter table stud_417 add (sum_evaluation number default 100);
alter table class_417 add(sum_evaluation number default 100);
commit;
二、建立上课考勤总表Attend_417
create table Attend_417
(
riqi date,
unit char,
sno varchar2(10) references stud_417(sno),
cno varchar2(10) references class_417(cno),
condition varchar2(10),
primary key(riqi,unit,sno)
);
alter table attend_417 add constraint ck_attend_con check2016/5/7
(contidion in ('正常','迟到','请假','旷课'));
alter table attend_417 add constraint ck_attend_unit check
(unit in ('1','2','3','4','5'));
三、建立个人考勤汇总表stud_attend和班级考勤汇总表class_attend。
1.个人考勤汇总表stud_attend
create table stud_attend
(
sno varchar2(10) primary key,
beginDate date,
endDate date,
zhengChangCiShu number,
chiDaoCiShu number,
kuangKeCiShu number,
qingJiaCiShu number,
koufen number
);
2.班级考勤汇总表class_attend
create table class_attend
(
cno varchar2(10) primary key,
beginDate date,
endDate date,
zhengChangCiShu number,
chiDaoCiShu number,
kuangKeCiShu number,
qingJiaCiShu number,
koufen number
);
/*修改主键 为cno,beginDate,endDate*/
三、建立触发器,当对考勤表Attend表进行相应插入、删除、修改时,对stud表的sum_evaluation 数值进行相应的数据更新。
create or replace trigger attend_stud
before insert or update or delete on Attend_417
for each row
begin
if inserting then
if :new.condition = '迟到' then
update stud_417 set sum_evaluation=sum_evaluation-2 where :new.sno = stud_417.sno;