数据库触发器控制-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

数据库触发器控制

简介:
①选修成绩表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/

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章