SQL觸發(fā)器作業(yè)(共3頁)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上1.為SC創(chuàng)建一個觸發(fā)器,當(dāng)插入或者修改一個記錄時候,確保此記錄的成績在0-100之間.Create trigger Score_sc_tri On SC For Insert,update As declare score_read tinyint Select score_read=score from inserted If score_read>=0 and score_read<=100 begin Print ' 操作完成!' Return End Print '成績超出之間！請重新輸入.' Rollback T

2、ransactionINSERT INTO SC VALUES('S1','C5',190) GO INSERT INTO SC VALUES('S1','C5',100)GOUPDATE sc SET Score=130 WHERE sno='S2'AND cno='C5'GOUPDATE sc SET Score=60 WHERE sno='S2'AND cno='C5'GO2.為T創(chuàng)建一個觸發(fā)器,男職工年齡不超過60周歲,女職工是教授的年齡不超過60,其他女職工

3、不超過55.create trigger trigger_Teacher on Teacher for update ,insert as declare Age_new int,sex char(2),prof char(10)select Age_new=age from insertedselect sex=sex ,prof=prof from Teacherif sex='男'and Age_new<=60and Age_new>0 begin print '操作完成' return end else if sex='女'a

4、nd prof='教授' and Age_new<=60and Age_new>0 begin print '操作完成' return endelse if sex='女'and prof<>'教授' and Age_new<=55and Age_new>0 begin print '操作完成' return endprint '記錄數(shù)據(jù)錯誤！請重新輸入。'rollback TransactionINSERT INTO Teacher VALUES('S1

5、','C5','男','63','','','','') GO 3.為TCCSC創(chuàng)建參照完整性,級聯(lián)刪除和級聯(lián)修改觸發(fā)器.為表C創(chuàng)建一級聯(lián)更新觸發(fā)器TRIGGER_Course。CREATE TRIGGER TRIGGER_Course ON course FOR UPDATE AS IF UPDATE(cno) BEGIN DECLARE cno_NEW CHAR(2),cno_OLD CHAR(2)SELECT cno_NEW=cno FROM INSERTEDSELEC

6、T cno_OLD=cno FROM DELETEDUPDATE SC SET cno=cno_NEW WHERE cno=cno_OLDUPDATE TC SET cno=cno_NEW WHERE cno=cno_OLD END為表SC創(chuàng)建一限制更新觸發(fā)器TRIGGER_SC要求：若修改SC表中一記錄的課號，則要檢查表course中是否存在與該課號相同的記錄，若有則不許修改，若沒有則可修改。CREATE TRIGGER TRIGGER_SC ON SC FOR UPDATE AS IF UPDATE(cno) BEGINDECLARE cno_NEW CHAR(5),cno_OLD CHA

7、R(5),cno_CNT INTSELECT cno_OLD=cno FROM DELETEDSELECT cno_CNT=COUNT(*) FROM course WHERE cno=cno_OLDIF cno_CNT<>0 ROLLBACK TRANSACTION END為表TC創(chuàng)建一限制更新觸發(fā)器TRIGGER_TC要求：若修改TC表中一記錄的課號，則要檢查表course中是否存在與該課號相同的記錄，若有則不許修改，若沒有則可修改。CREATE TRIGGER TRIGGER_TC ON TC FOR UPDATE AS IF UPDATE(cno) BEGINDECLARE

8、 cno_NEW CHAR(5),cno_OLD CHAR(5),cno_CNT INTSELECT cno_OLD=cno FROM DELETEDSELECT cno_CNT=COUNT(*) FROM course WHERE cno=cno_OLDIF cno_CNT<>0 ROLLBACK TRANSACTION END用SQL為表C創(chuàng)建一個級聯(lián)刪除觸發(fā)器TRIGGER_DC：通過課程名從C表中刪除某課程信息，同時刪除SC,TC表中與此課程相關(guān)的選課記錄USE jiaoxuedbGOCreate trigger TRIGGER_DCourse On Course For

9、DELETE As declare CNO_DEL CHAR(5)Select CNO_DEL=CNO From DELETEDDELETE FROM SC WHERE SC.CNO=CNO_DEL DELETE FROM TC where TC.cno=CNO_DEL GO（）驗證觸發(fā)器的作用。Delete from Course where CNO= '01001' GOINSERT INTO Course VALUES('01001',' 信息與計算科學(xué)','90','4.0') GO INSERT INTO SC VALUES('S1','C5',100)G