qileilove

blog已经转移至github,大家请访问 http://qaseven.github.io/

数据库触发器控制

①选修成绩表SCTS中，学生选修成绩由平时成绩(regular_grade)和考试成绩(exam_grade)构成课程总评成绩(total_mark)，公式为：total_mark= regular_grade*30%+ exam_grade*70%
　　设计DML触发器，使得当用户修改某位学生选修某门课程的平时成绩或者考试成绩时，自动实现对该学生该门课程总评成绩的更新。
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
　　--测试触发器
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、②设计DML触发器，使得当某学生在一个学期中所选修的课程总学分超过20学分时，自动提示“你选修的总学分已达到最大值，不允许继续选课！”的信息；
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'你选修的总学分已达到最大值，不允许继续选课！'
end
else
print'选课成功'
END
END
　--测试触发器
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.设计DML触发器限定：对于“专业”（COURSES.character，包括专业课、专业基础、专业选修等）课程，只有该课程开课学院的学生才能选修，否则提示“不允许跨院选课！”的提示信息。
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 '专业%')
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 '选专业课成功'
else
BEGIN
Rollback Transaction
PRINT '不允许跨院选课！'
END
END
ELSE
PRINT '选修公共选修课成功'
END
　　--选修本学院专业课
　　insert into Scts(courseid,studentid,teacherid)
　　values ('20224B0','200520805403','080102');
　　--选修非本院专业课 终止
　　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 '专业%')and college
='08'
select *from scts where studentid='200520805403'
delete from scts  where  courseid='20224B0' and  studentid='200520805403';
　　--4、设计DML触发器以实现对敏感数据的自动审计：当用户在SCTS表中插入新记录或者更新SCTS表中的regular_grade和exam_grade属性列时，自动在成绩变化表GRADE_LOG（student, course, teacher, regular_grade, exam_grade, username, userdate）中增加一条相应记录，以记录当前用户对学生成绩的操作。(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'
　　--只更新总成绩，不激活触发器
　　update  Scts
　　set total_mark='100'
　　where studentid='200520805403'and courseid='20224B0' and teacherid='080102'
　　--5、DDL触发器，禁止用户在Teaching数据库中的修改表和删除表操作。
CREATE TRIGGER TRI_Teaching_DDL
ON database
for alter_table,drop_table
AS
BEGIN
print '不允许修改或删除数据表！'
Rollback Transaction
END

posted on 2014-05-04 13:03 顺其自然EVO 阅读(168) 评论(0)  编辑  收藏 所属分类: 测试学习专栏 、数据库


只有注册用户登录后才能发表评论。
网站导航:
 
<2014年5月>
日一二三四五六
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567

导航

统计

常用链接

留言簿(55)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜