级联删除

--1.创建一触发器实现D表,t表,s表,sc表现级联删除。
if exists(select * from sysobjects where name ='delete_dstc'and type='tr')
drop trigger delete_dstc
go
create trigger delete_dstc on d
instead of delete
as
begin tran
delete from t where dnum in (select dnum from deleted)
delete from sc where snum in (select snum from s where dnum in(select dnum from deleted))
delete from s where dnum in (select dnum from deleted)
delete from d where dnum in (select dnum from deleted)
if @@error = 0
begin
raiserror('删除记录成功',16,1)
end
else
begin
raiserror('删除记录出错',16,1)
rollback tran
end

--2.在选课表ＳC上创建触发器，当向该表中插入记录时，若学生选课门数超过５门时，则向客户端发出警告信息，同时将插入记录自动删除。
if exists(select * from sysobjects where name ='insert_sc'and type='tr')
drop trigger insert_sc
go
create trigger insert_sc
on sc
for insert
as if(select COUNT(*)from inserted,sc where sc.snum=inserted.snum )>5
begin raiserror('对不起，您选课门数超过５门',16,1)
rollback transaction
end
else raiserror('记录成功插入',16,1)
go