数据库高级数据库学习--上机练习9-2(触发器)

上机练习9—触发器综合练习
1、 创建一个触发器,使新插入记录中学号位数保持5位且必须以“14”开头,否则撤销相应操作; CREATE TRIGGER Sno_insert ON Student FOR INSERT AS DECLARE @Sno char(10) SELECT @Sno = Sno FROM inserted IF(LEN(@Sno)!=5 or (@Sno NOT LIKE '14%')) BEGIN RAISERROR ('学号不符合条件',16,1) ROLLBACK TRANSACTION END

2、 创建一个触发器,防止SC表中的成绩被随意修改; CREATE TRIGGER Grade_update ON Score FOR INSERT AS IF UPDATE(Cscore) BEGIN print '修改失败.....' RAISERROR ('安全警告:成绩不能修改',16,1) ROLLBACK TRANSACTION END GO

3、 向SC表插入或修改1条记录,通过触发器检查学号和课程号在该表中是否存在,若存在则取消插入或修改操作; CREATE TRIGGER SC_ins ON Score FOR INSERT AS DECLARE @sno varchar(10), @cno char(4), @cscore decimal(3,1) SELECT @sno = Sno,@cno = Cno,@cscore = Cscore FROM inserted IF(@sno in(SELECT Sno FROM Score WHERE @cno=Cno)) PRINT('插入失败') ELSE INSERT INTO Score(Sno,Cno,Cscore) VALUES(@sno,@cno,@cscore) ROLLBACK TRANSACTION GO

4、 创建触发器实现:当向SC表插入记录时,分别检查学号及课程号在Student表和Course表中是否存在,若不存在则阻止该操作; create Trigger SC_ins2 on Score for insert as declare @sno varchar(10),@cno char(4),@cscore decimal(3,1) select @sno = Sno,@cno = Cno,@cscore = Cscore from inserted if(@sno in(select Sno from Score) and @cno in(select Cno from Course)) insert into Score(Sno,Cno,Cscore) values(@sno,@cno,@cscore) else print('插入失败') rollback transaction go

5、 创建触发器实现如下功能:若Course表中的课程号发生变化,在SC表中的对应课程号也相应变化,如课程号“1”变成“10001”,则SC表中相应值也同时变化; create trigger course_change on Course for update as declare @cno_new char(10),@cno_old char(10) select @cno_new = Cno from inserted select @cno_old = Cno from deleted update Score set Cno = @cno_new where Cno = @cno_old go

6、 在学生信息表Student上创建触发器,当数据删除学生记录时,判断如果该学生已经有考试成绩,则不允许删除; create trigger del_stu on Student for delete as declare @sno varchar(10) select @sno = Sno from deleted if(@sno in (select Sno from Score)) print('成绩不得随意修改') else begin delete from Student where Sno = @sno end rollback transaction go

7、 创建触发器实现:当删除学生表中的学生记录时,同步删除选课表中该学生的选课信息; create trigger del_stu2 on Student for delete as declare @sno varchar(10) select @sno = Sno from deleted delete from Score where Sno = @sno go

8、 创建触发器防止用户在删除Student表中信息时进行的误操作(删除记录数大于1),如:执行了delete from Student (忘了加where限制条件)而删除了全部数据。 create Trigger del_stu3 on Student for delete as declare @count int select @count = count(Sno) from deleted if(@count > 1) print('删除失败') rollback transaction go

 

上一篇:Mysql建库建表及查询


下一篇:SQL基础语句