国产一区二区三区的电影,91.xxx.高清在线,超碰激情在线

blog已經(jīng)轉(zhuǎn)移至github,大家請(qǐng)?jiān)L問(wèn) http://qaseven.github.io/

數(shù)據(jù)庫(kù)觸發(fā)器控制

①選修成績(jī)表SCTS中，學(xué)生選修成績(jī)由平時(shí)成績(jī)(regular_grade)和考試成績(jī)(exam_grade)構(gòu)成課程總評(píng)成績(jī)(total_mark)，公式為：total_mark= regular_grade*30%+ exam_grade*70%

　　設(shè)計(jì)DML觸發(fā)器，使得當(dāng)用戶修改某位學(xué)生選修某門課程的平時(shí)成績(jī)或者考試成績(jī)時(shí)，自動(dòng)實(shí)現(xiàn)對(duì)該學(xué)生該門課程總評(píng)成績(jī)的更新。

Create trigger Tri_UPDATE_SCTS

on SCTS

after UPDATE

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

　　--測(cè)試觸發(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è)計(jì)DML觸發(fā)器，使得當(dāng)某學(xué)生在一個(gè)學(xué)期中所選修的課程總學(xué)分超過(guò)20學(xué)分時(shí)，自動(dòng)提示“你選修的總學(xué)分已達(dá)到最大值，不允許繼續(xù)選課！”的信息；

Create trigger Tri_IN_LIMIT_SCTS

on SCTS

after insert --注意這里AFTER是插入之后的

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'你選修的總學(xué)分已達(dá)到最大值，不允許繼續(xù)選課！'

end

else

print'選課成功'

END

　--測(cè)試觸發(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è)計(jì)DML觸發(fā)器限定：對(duì)于“專業(yè)”（COURSES.character，包括專業(yè)課、專業(yè)基礎(chǔ)、專業(yè)選修等）課程，只有該課程開(kāi)課學(xué)院的學(xué)生才能選修，否則提示“不允許跨院選課！”的提示信息。

Create trigger Tri_INSERT_SCTS

on SCTS

after insert

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

ELSE

PRINT '選修公共選修課成功'

END

　　--選修本學(xué)院專業(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è)計(jì)DML觸發(fā)器以實(shí)現(xiàn)對(duì)敏感數(shù)據(jù)的自動(dòng)審計(jì)：當(dāng)用戶在SCTS表中插入新記錄或者更新SCTS表中的regular_grade和exam_grade屬性列時(shí)，自動(dòng)在成績(jī)變化表GRADE_LOG（student, course, teacher, regular_grade, exam_grade, username, userdate）中增加一條相應(yīng)記錄，以記錄當(dāng)前用戶對(duì)學(xué)生成績(jī)的操作。(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

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

select * from GRADE_LOG;

　　--更新成績(jī)

　　update Scts

　　set regular_grade='100',exam_grade='20'

　　where studentid='200520805403'and courseid='20224B0' and teacherid='080102'

　　--只更新總成績(jī)，不激活觸發(fā)器

　　update Scts

　　set total_mark='100'

　　where studentid='200520805403'and courseid='20224B0' and teacherid='080102'

　　--5、DDL觸發(fā)器，禁止用戶在Teaching數(shù)據(jù)庫(kù)中的修改表和刪除表操作。

CREATE TRIGGER TRI_Teaching_DDL

ON database

for alter_table,drop_table

BEGIN

print '不允許修改或刪除數(shù)據(jù)表！'

Rollback Transaction

END

posted on 2014-05-04 13:03 順其自然EVO 閱讀(170) 評(píng)論(0) 編輯收藏所屬分類: 測(cè)試學(xué)習(xí)專欄、數(shù)據(jù)庫(kù)

新用戶注冊(cè) 刷新評(píng)論列表


只有注冊(cè)用戶登錄后才能發(fā)表評(píng)論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問(wèn) 管理
相關(guān)文章: 如何進(jìn)行Web服務(wù)的性能測(cè)試？利用drozer進(jìn)行Android滲透測(cè)試 Appium Android Bootstrap源碼分析之命令解析執(zhí)行 Fliptest—iOS 的應(yīng)用A/B測(cè)試框架 iOS功能測(cè)試工具 Frank iOS單元測(cè)試框架Kiwi for iOS Appium Android Bootstrap之控件AndroidElement 移動(dòng)應(yīng)用測(cè)試框架—Calabash Android 簡(jiǎn)介 Appium Server源碼分析之作為Bootstrap客戶端移植MonkeyRunner的圖片對(duì)比功能實(shí)現(xiàn)-Appium篇

qileilove

數(shù)據(jù)庫(kù)觸發(fā)器控制

導(dǎo)航

統(tǒng)計(jì)

常用鏈接

留言簿(55)

隨筆分類

隨筆檔案

文章分類

文章檔案

搜索

最新評(píng)論

閱讀排行榜

評(píng)論排行榜