oracle-触发器练习

触发器课堂练习11、当向SC表插入数据时，修改（或添加）SC_NUMBER（包括学生学号和选课门数两列）表格中的数据。createorreplacetriggertr_sc_AR_Iafterinsertonscforeachrowbeginupdatesc_numbersetscnum=scnum+1wheresno=:new.sno;ifsql%notfoundtheninsertintosc_numbervalues(:new.sno,1);endif;end;测试结果：2、当删除SC表中数据时，修改（或添加）SC_NUMBER中的数据。createorreplacetriggertr_sc_AR_Dafterdeleteonscforeachrowbeginupdatesc_numbersetscnum=scnum-1wheresno=:old.sno;selectscnumintopk_trigger.tr_scnumfromsc_numberwheresno=:old.sno;if(pk_trigger.tr_scnum=0)thendeletefromsc_numberwheresno=:old.sno;endif;end;测试结果：3、当修改SC表中数据时，若修改的是学号，则对应修改SC_NUMBER表中的选课门数，否则打印“某某（学生）的学生选课信息已经修改”信息。createorreplacetriggertr_sc_AR_Uafterupdateonscforeachrowbeginif(:old.sno=:new.sno)thenselectsnameintopk_trigger.tr_snamefromstudentwheresno=:new.sno;dbms_output.put_line(pk_trigger.tr_sname||'的学生选课信息已经修改,由原课程'||trim(:old.cno)||'修改为新课程'||trim(:new.cno));endif;if(:old.sno:new.sno)thenupdatesc_numbersetscnum=scnum+1wheresno=:new.sno;ifsql%notfoundtheninsertintosc_numbervalues(:new.sno,1);endif;updatesc_numbersetscnum=scnum-1wheresno=:old.sno;selectscnumintopk_trigger.tr_scnumfromsc_numberwheresno=:old.sno;if(pk_trigger.tr_scnum=0)thendeletefromsc_numberwheresno=:old.sno;endif;endif;end;测试结果：触发器课堂练习21、修改STUDENT表数据时，限制不能修改学生的系别（不能修改CS系学生的系别）。createorreplacetriggertr_student_BR_U1beforeupdateofsdeptonstudentforeachrowwhen(old.sdept='CS')--old前没有:beginraise_application_error(-20001,'不能修改CS系学生的系别！');end;测试结果：2、插入课程时，课程号以‘S’开头的课程的学分不能低于3分。createorreplacetriggertr_course_BR_Ibeforeinsertoncourseforeachrowwhen(new.credit3andnew.cnolike'S%')beginraise_application_error(-20002,'S开头的课程学分不能低于3分！');end;测试结果：3、不能删除90分以上学生的选课信息。createorreplacetriggertr_sc_BR_Dbeforedeleteonscforeachrowwhen(old.grade90)beginraise_application_error(-20003,'不能删除90分以上学生的选课信息！');end;测试结果：练习11、插入Student表中数据时，CS系学生的年龄不能大于30岁。createorreplacetriggertr_student_BR_Ibeforeinsertonstudentforeachrow--when(new.sage30andnew.sdept='CS')beginif(:new.sage30and:new.sdept='CS')thenraise_application_error(-20004,'CS系学生的年龄不能大于30岁!');endif;end;测试结果：2、当修改Student表中的年龄字段时，使其只能增加，不能减少。createorreplacetriggertr_student_BR_Ubeforeupdateofsageonstudentforeachrowwhen(new.sageold.sage)beginraise_application_error(-20005,'年龄只能增加不能减少！');end;测试结果：3、删除Student表中的学生信息时，判断在SC表中该学生的平均成绩是否高于60，若高于60，则不能删除，否则允许删除，同时删除SC表该学生对应的选课信息。createorreplacetriggertr_student_BR_Dbeforedeleteonstudentforeachrowdeclareavg_scorenumber;beginselectavg(grade)intoavg_scorefromscwheresc.sno=:old.sno;if(avg_score60)thenraise_application_error(-20006,'不能删除平均分大于60的学生信息！');endif;if(avg_score=60)thendeletefromscwheresc.sno=:old.sno;endif;end;测试结果：练习2在Student表中添加列：sum_Grade（总成绩）,avg_grade（平均成绩）。在SC表中作一触发器，当添加，删除或修改一行之后，将该学生在Student表中的总成绩和平均成绩相应改变。createorreplacepackagepk_triggeristr_scnumsmallint;--sc_number表中的当前选课数量tr_snamestudent.sname%type;tr_cnosc.cno%type;--记录正在更新的课程号tr_newsnosc.sno%type;--记录更新后的学生号tr_oldsnosc.sno%type;--记录更新前的学生号endpk_trigger;createorreplacetriggertr_sc_BR_I_U_Dbeforeinsertorupdateordeleteonscforeachrowbeginpk_trigger.tr_newsno:=:new.sno;pk_trigger.tr_oldsno:=:old.sno;end;createorreplacetriggertr_sc_AL_I_U_Dafterinsertorupdateordeleteonscdeclarev_sumnumber;v_avgnumber;begin--更新变化之前的学生的总分和平均成绩selectsum(grade),avg(grade)intov_sum,v_avgfromscwheresno=pk_trigger.tr_oldsno;updatestudentsetsum_grade=v_sum,avg_grade=v_avgwheresno=pk_trigger.tr_oldsno;--更新变化之后的学生总分和平均分selectsum(grade),avg(grade)intov_sum,v_avgfromscwheresno=pk_trigger.tr_newsno;updatestudentsetsum_grade=v_sum,avg_grade=v_avgwheresno=pk_trigger.tr_newsno;end;测试结果：修改成绩修改学号删除练习3插入或修改（修改课程号时）选课信息时，若该课程的选课人数已满，则不允许操作，并抛出相应的错误提示。createorreplacepackagepk_triggeristr_scnumsmallint;--sc_number表中的当前选课数量tr_snamestudent.sname%type;tr_cnosc.cno%type;--记录正在更新的课程号tr_newsnosc.sno%type;--记录更新后的学生号tr_oldsnosc.sno%type;--记录更新前的学生号endpk_trigger;createorreplacetriggertr_sc_BR_IUbeforeinsertorupdateonscforeachrowbeginpk_trigger.tr_cno:=:new.cno;end;createorreplacetriggertr_sc_AL_IUafterinsertorupdateonscdeclarev_snumbersmallint;v_scntsmallint;begin--查询课程最大选课人数selectsnumberintov_snumberfromcoursewhereo=pk_trigger.tr_cno;--查询当前选课人数selectcount(sno)intov_scntfromscwhereo=pk_trigger.tr_cno;--如果选课人数已满，则抛出相应错误提示if(v_snumberv_scnt)thenraise_application_error(-20008,'课程'||trim(pk_trigger.tr_cno)||'选课人数已满!');endif;end;测试结果：