qileilove
          

          blog已經(jīng)轉(zhuǎn)移至github,大家請訪問 http://qaseven.github.io/
          
			
          
				
					
	
          
		
          
			數(shù)據(jù)庫觸發(fā)器控制
		
          
		
          ①選修成績表SCTS中，學生選修成績由平時成績(regular_grade)和考試成績(exam_grade)構(gòu)成課程總評成績(total_mark)，公式為：total_mark= regular_grade*30%+ exam_grade*70%
          　　設(shè)計DML觸發(fā)器，使得當用戶修改某位學生選修某門課程的平時成績或者考試成績時，自動實現(xiàn)對該學生該門課程總評成績的更新。
          Create trigger Tri_UPDATE_SCTS
          on SCTS
          after UPDATE
          AS
          BEGIN
          IF UPDATE(regular_grade)or UPDATE(exam_grade)
          BEGIN
          DECLARE @rgrade float;
          DECLARE @egrade float;
          select @rgrade=regular_grade,@egrade= exam_grade from inserted
          update SCTS
          set total_mark=0.3*@rgrade+0.7*@egrade
          from inserted
          where SCTS.studentid =inserted.studentid
          and SCTS.courseid=inserted.courseid
          and SCTS.teacherid =inserted.teacherid
          END
          END
          　　--測試觸發(fā)器
          update  Scts
          set regular_grade='100',exam_grade='100'
          where studentid='200520805403'and courseid='20224B0' and teacherid='080102'
          select *from scts
          where studentid='200520805403'and courseid='20224B0' and teacherid='080102'
          　　--2、②設(shè)計DML觸發(fā)器，使得當某學生在一個學期中所選修的課程總學分超過20學分時，自動提示“你選修的總學分已達到最大值，不允許繼續(xù)選課！”的信息；
          Create trigger Tri_IN_LIMIT_SCTS
          on SCTS
          after insert --注意這里AFTER是插入之后的
          AS
          BEGIN
          BEGIN
          DECLARE @allcredit float;
          DECLARE @sid varchar(12);
          select @sid =inserted.studentid from inserted
          select  @allcredit=sum(credit)
          from courses
          where courseid in(
          select courseid
          from scts
          where studentid=@sid
          )
          if (@allcredit>20)
          begin
          Rollback Transaction
          print @allcredit
          print'你選修的總學分已達到最大值，不允許繼續(xù)選課！'
          end
          else
          print'選課成功'
          END
          END
          　--測試觸發(fā)器
          select  sum(credit) '總分'
          from courses
          where courseid in(
          select courseid
          from scts
          where studentid='200520701201'
          )
          insert into Scts(courseid,studentid,teacherid)
          values ('50103Q0','200520701201','080102');
          select *from courses where courseid='10042B0'
          　　--3.設(shè)計DML觸發(fā)器限定：對于“專業(yè)”（COURSES.character，包括專業(yè)課、專業(yè)基礎(chǔ)、專業(yè)選修等）課程，只有該課程開課學院的學生才能選修，否則提示“不允許跨院選課！”的提示信息。
          Create trigger Tri_INSERT_SCTS
          on SCTS
          after insert
          AS
          BEGIN
          DECLARE @stype varchar(10);
          DECLARE @collegeid varchar(5);
          if exists(select * from courses AS C
          where C.courseid in(select courseid  from inserted ) and C.Character like '專業(yè)%')
          BEGIN
          select @collegeid=college from courses AS C
          where C.courseid in(select courseid  from inserted )
          if exists (select college from students AS S
          where S.studentid in(select studentid  from inserted )and S.college=@collegeid)
          PRINT '選專業(yè)課成功'
          else
          BEGIN
          Rollback Transaction
          PRINT '不允許跨院選課！'
          END
          END
          ELSE
          PRINT '選修公共選修課成功'
          END
          　　--選修本學院專業(yè)課
          　　insert into Scts(courseid,studentid,teacherid)
          　　values ('20224B0','200520805403','080102');
          　　--選修非本院專業(yè)課 終止
          　　insert into Scts(courseid,studentid,teacherid)
          　　values ('10019B5','200520805403','080102');
          　　--選修公共選修課
          insert into Scts(courseid,studentid,teacherid)
          values ('50095Q0','200520805403','080102');
          select *from students where college='08'
          select *from colleges where collegeid='08'
          select *from courses where courseid in (select courseid from courses where character not like '專業(yè)%')and college
          ='08'
          select *from scts where studentid='200520805403'
          delete from scts  where  courseid='20224B0' and  studentid='200520805403';
          　　--4、設(shè)計DML觸發(fā)器以實現(xiàn)對敏感數(shù)據(jù)的自動審計：當用戶在SCTS表中插入新記錄或者更新SCTS表中的regular_grade和exam_grade屬性列時，自動在成績變化表GRADE_LOG（student, course, teacher, regular_grade, exam_grade, username, userdate）中增加一條相應(yīng)記錄，以記錄當前用戶對學生成績的操作。(system_user)
          Create table GRADE_LOG(
          id int  identity(1,1) primary key,
          student varchar(20) not null ,
          course  varchar(50) not null,
          teacher varchar(20) not null,
          regular_grade float ,
          exam_grade float,
          username  varchar(20) not null,
          userdate datetime not null,
          operator varchar(10) not null
          )
          --select system_user  getdate()
          Create trigger  Tri_IN_U_SCTS
          on SCTS
          after INSERT,UPDATE
          AS
          BEGIN
          IF UPDATE(regular_grade)OR UPDATE(exam_grade)or (exists (select 1 from inserted) and not exists (select 1
          from deleted))
          BEGIN
          DECLARE @student varchar(20);
          DECLARE @course varchar(50);
          DECLARE @teacher varchar(20);
          DECLARE @rgrade float;
          DECLARE @egrade float;
          DECLARE @username  varchar(20);
          DECLARE @date datetime;
          DECLARE @type varchar(10);
          select @type='update';
          if exists (select 1 from inserted) and not exists (select 1 from deleted)
          select @type='insert';
          select @student=sname from students where studentid in(select studentid  from inserted )
          select @course =cname from courses where courseid in (select courseid from inserted)
          select @teacher =tname from teachers where teacherid in (select teacherid from inserted)
          select @rgrade=regular_grade,@egrade=exam_grade from inserted
          select @username=system_user,@date=getdate();
          insert into GRADE_LOG
          values(@student,@course,@teacher,@rgrade,@egrade,@username,@date,@type)
          END
          END
          select * from  GRADE_LOG;
          　　--更新成績
          　　update  Scts
          　　set regular_grade='100',exam_grade='20'
          　　where studentid='200520805403'and courseid='20224B0' and teacherid='080102'
          　　--只更新總成績，不激活觸發(fā)器
          　　update  Scts
          　　set total_mark='100'
          　　where studentid='200520805403'and courseid='20224B0' and teacherid='080102'
          　　--5、DDL觸發(fā)器，禁止用戶在Teaching數(shù)據(jù)庫中的修改表和刪除表操作。
          CREATE TRIGGER TRI_Teaching_DDL
          ON database
          for alter_table,drop_table
          AS
          BEGIN
          print '不允許修改或刪除數(shù)據(jù)表！'
          Rollback Transaction
          END
          
		
          
			posted on 2014-05-04 13:03 順其自然EVO 閱讀(169) 評論(0)  編輯  收藏  所屬分類: 測試學習專欄 、數(shù)據(jù)庫 
		
          
	
          
	
	


	


          
	    
    




          








          

				
			
          
			
          
				
					
          
	
          
		
          <2014年5月>
          
	
          日一二三四五六
          27282930123
          45678910
          11121314151617
          18192021222324
          25262728293031
          1234567
          

          

					

          導(dǎo)航
          

					

          統(tǒng)計
          
	
					
					

          常用鏈接
          


          留言簿(55)
          


		
          隨筆分類
          
		
				
			
	
		
          隨筆檔案
          
		
				
			
	
		
          文章分類
          
		
				
			
	
		
          文章檔案
          
		
				
			
	



          搜索
          



          最新評論
	
          



          閱讀排行榜
          



          評論排行榜
          


				
			
          			
			

	

    







          
        
	




主站蜘蛛池模板：
崇阳县|
城步|
安徽省|
福泉市|
武安市|
泾川县|
靖江市|
礼泉县|
景宁|
黄大仙区|
辽中县|
闽侯县|
辽宁省|
托里县|
上虞市|
乌拉特中旗|
哈巴河县|
布尔津县|
方正县|
松原市|
河北区|
吐鲁番市|
绥江县|
商丘市|
霍州市|
浦北县|
商都县|
万源市|
天长市|
定西市|
永安市|
巫溪县|
唐海县|
肇东市|
阳东县|
昌都县|
泾川县|
西乡县|
专栏|
邵武市|
灌云县|