上机练习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