--(1)创建后触发器每当用户在SC表中插入一行数据时,就显示一个提示信息。
create trigger t1
on sc
after insert
as
print '向sc插入了一行数据';
--(2)创建后触发器限定Course表中Semester的取值范围为1-10。
create trigger t2
on course
after update,insert
as
if exists(select * from inserted where Semester not between 1 and 10)
rollback;
--(3)创建后触发器限制每个学期开设的课程总数不能超过10门,如果超过了10门,则给出提示信息:本学期课程太多。
create trigger t3
on course
after insert
as
if(select COUNT(*) from Course c join inserted i on i.Semester=c.Semester) > 10
begin
print '本学期课程太多'
rollback
end;
--(4)创建后触发器在SC表中,不能将不及格的考试成绩改为及格。
create trigger t4
on sc
after update
as
if exists(select * from inserted i join deleted d on i.Cno=d.Cno and i.Sno=d.Sno where i.Grade>=60 and d.Grade <60)
begin
print '不能将不及格的考试成绩改为合格'
rollback
end;
--(5)创建前触发器在SC表中,不能删除考试成绩不及格学生的该门课程的考试记录。
create trigger t5
on sc
instead of delete
as
if not exists(select * from deleted where Grade<60)
delete from SC where Sno in (select Sno from deleted ) and Cno in (select Cno from deleted);
-- 适用于一次删除一条元组
CREATE Trigger tri_DeleteSC1
ON SC INSTEAD OF DELETE
AS
IF NOT EXISTS(SELECT * FROM DELETED
WHERE Grade < 60 )
Begin
Select * from deleted
DELETE FROM SC
WHERE Sno IN( SELECT Sno FROM DELETED )
AND Cno IN( SELECT Cno FROM DELETED )
End
-- 一次删除多条元组
create Trigger tri_DeleteSC1
ON SC INSTEAD OF DELETE
AS
IF NOT EXISTS(SELECT * FROM DELETED
WHERE Grade < 60 )
begin
select * from deleted
DELETE FROM SC
from sc1 join deleted d on sc.sno=d.sno and sc.cno=d.cno
end
-- 测试更新命令
delete from sc where sno='0811101' and cno='C001'
delete from sc where cno='C002'
delete from sc where sno='0811103'
delete from sc where grade=80
--(6)删掉course表中的课程信息,请使用SQL语句做出相应处理(设系统对于参照完整性的策略为拒绝删除)。
create Trigger DEL_course
ON Course INSTEAD OF delete
AS
-- if exists(select * from sc where cno in
--(select cno from deleted))
-- begin
delete from sc where cno in
(select cno from deleted)
-- end
delete from course where cno in
(select cno from deleted)
--测试更新命令
delete from course where cname='计算机网络'
delete from course where cname='Java'
--*(7)创建触发器防止在Students数据库中删除和更改任何表。
create trigger t7
on database
for drop_table ,alter_table
as
print'不能在Students中删除或更改表!'
rollback;