①选修成绩表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
|
最新内容请见作者的GitHub页：http://qaseven.github.io/
