中的相应记录也删除。
create trigger sno_delete on student after delete as begin
declare @delect_sno char(9)
select @delect_sno=sno from deleted
PRINT'已经级联删除sc表中学号为'+@delect_sno+'的学生成绩信息' delete from sc where sno in(select sno from deleted) end
/*测试*/
delete from student where sno='200515004'
4)创建INSTEAD OF触发器,当向sc表中插入记录时,先检查sno列上的值在student中是否存在,如果存在执行插入操作,如果不存在则提示“该学生不存在”。
create trigger sno_exists on sc instead of insert as begin
declare @sno char(9)
Select @sno=sno from inserted
if(@sno in(select sno from student)) insert into sc select * from inserted else
Print'该学生不存在' End
--测试 插入不存在的值
insert into sc values('200515036','05',78)
5)比较约束与触发器的执行顺序。(在一个表中创建CHECK约束和触发器,然后向表中插入一条不符合约束和触发器的记录,查看谁先发生作用。)
alter table course add constraint CK_xf
check (ccredit>=1 and ccredit<=6)
--测试
insert into course values('13','C#程序设计',null,8)
从这部分实验中可以看到,约束优先于FOR或AFTER触发器起作用,它在更新前就生效,对要更新的值进行规则检查。当检查到与现有规则冲突时,系统给出错误消息,并取消更新操作。如果检查没有问题,更新被执行,再激活触发器。