【正文】 , conAddress varchar(50), StuID varchar(10), conID varchar(10) select conID = conID, conName = conName, conTime = conTime, conAddress = conAddress from inserted declare stuID_cursor cursor for select conStuID from conResult where conID = conID open stuID_cursor fetchnextfrom stuID_cursor into stuID while(FETCH_STATUS= 0) begin insertinto notice ( noticeTime, noticeHostID, noticeInfo ) values ( dateadd(minute, 5,getdate()), 39。+cast(rank asvarchar) ) insertinto stuNotice ( stuNotID, stuNotTo ) values ( (selectident_current(39。+cast(score asvarchar)+39。+ conName +39。, 39。go圖413圖414droptrigger result_noticegocreatetrigger result_notice on conResult afterupdateas if(update(conStuRank)) begin declare score real, rank int, conName varchar(30), StuID varchar(10), conID varchar(10) select conID = conID, stuID = conStuID, score = conStuScore, rank = conStuRank from inserted select conName = conName from conResult, conInfor where = insertinto notice ( noticeTime, noticeHostID, noticeInfo ) values ( dateadd(minute, 5,getdate()), 39。)), noticeTo ) endgoexec verify_contest 6, 39。+ newVerify ) insertinto teaNotice ( teaNotID, teaNotTo ) values ( (selectident_current(39。+ conName +39。, 39。, go/*成績排名過程*/dropproc con_rankgocreateprocedure con_rank conID intas begin declare n int, preRank int, score real, preScore real select n = 1, preRank = 1, preScore = 0 declare rank_cursor cursor forselect conStuScore from conResult where conID = conID orderby conStuScore desc open rank_cursor fetchnextfrom rank_cursor into score while(FETCH_STATUS= 0) begin if(score = preScore) begin update conResult set conStuRank = preRank wherecurrentof rank_cursor end else begin update conResult set conStuRank = n wherecurrentof rank_cursor select preRank = n end select preScore = score, n = n + 1 fetchnextfrom rank_cursor into score end close rank_cursor deallocate rank_cursor select* from conResult where conID = conID orderby conStuScore desc endgoexec con_rank3go圖412droptrigger verify_noticegocreatetrigger verify_notice on conInfor afterupdateas ifupdate(conVerify) begin declare newVerify varchar(10), noticeTo varchar(10), conName varchar(30) select newVerify = conVerify, noticeTo = conTeaID, conName = conName from inserted insertinto notice ( noticeTime, noticeHostID, noticeInfo ) values ( dateadd(minute, 5,getdate()), 39。go圖410圖411dropproc set_scorego/*錄入成績過程*/createprocedure set_score conID int, stuID char(10), result realas update conResult set conStuScore = result where conID = conID and conStuID = stuIDgoexec set_score 2, 39。, 39。goexec show_dep_contestgo圖49dropprocedure reg_contestgo/*報名參賽過程*/createprocedure reg_contest conID int, stuID char(10), Guide varchar(30)as insertinto conResult ( conID, conStuID, conStuGuiName ) values ( conID, stuID, Guide )goexec reg_contest 2, 39。goexec verify_contest4,39。go圖47圖48/*查看各學院申請比賽經(jīng)費情況*/createprocedure show_dep_contestas select conHost, sum(conBudget) totalBudget from conInfor groupby conHost, conVerify having conVerify =39。, 39。, 39。, 39。goexec con_notice 3, 39。, 39。, 39。, 39。 begin declare maj_cursor cursor forselect stuID from stuInfor where stuGrade = stuGrade and stuMajor = name open maj_cursor fetchnextfrom maj_cursor into stuID while(FETCH_STATUS=0) begin insertinto stuNotice ( stuNotID, stuNotTo ) values ( noticeID, stuID ) fetchnextfrom maj_cursor into stuID end close maj_cursor deallocate maj_cursor end end else return endgoexec con_notice 1, 39。 begin declare dep_cursor cursor forselect stuID from stuInfor, majInfor where stuGrade = stuGrade and = and = name open dep_cursor fetchnextfrom dep_cursor into stuID while(FETCH_STATUS=0) begin insertinto stuNotice ( stuNotID, stuNotTo ) values ( noticeID, stuID ) fetchnextfrom dep_cursor into stuID end close dep_cursor deallocate dep_cursor end if conLevel =39。 begin declare uni_cursor cursor forselect stuID from stuInfor where stuGrade = stuGrade open uni_cursor fetchnextfrom uni_cursor into stuID while(FETCH_STATUS=0) begin insertinto stuNotice ( stuNotID, stuNotTo ) values ( noticeID, stuID ) fetchnextfrom uni_cursor into stuID end close uni_cursor deallocate uni_cursor end if conLevel =39。) ) declare stuID char(10) if conLevel =39。 ) select noticeID = ( selectident_current(39。 begin declare conName varchar(30), conLevel varchar(20), noticeID int select conName = ( select conName from conInfor where conID = conID ) select conLevel = ( select conLevel