trigger.txt

用于在SQL 2005中创建触发器,功能完全,并附带有实验数据

create trigger coursetrigger
on course
for insert 
as 
declare @useridcount int
select @useridcount= count(*)
from inserted,teacher
where inserted.Userid=teacher.Userid;
if @UseridCount =0
 begin 
        print 'not' 
        rollback;
 end

insert into teacher
values(33,'001','zhangsan',80)

insert into course
values(34,'001','zhangsan',80)

insert into course
values(34,'002','Lisi',80)
不能插入
消息 3609，级别 16，状态 1，第 1 行
事务在触发器中结束。批处理已中止。


create trigger after
on student
for delete 
as 
declare @deletecount int
select @deletecount= count(*)
from deleted,sc
where deleted.sno=sc.sno;
if @deleteCount >0
 begin 
         delete sno
     from sc;
 end


DELETE
FRom student
where sno='200215121'
消息 208，级别 16，状态 1，过程 after，第 11 行
对象名  'sno' 无效。

create trigger after
on student
for delete 
as
delete sc
 from deleted,student
 where deleted.sno=sc.sno
(当student表中有相应记录，在sc表中也有时，那么student和sc中的记录会一起删，但是如果student中的相关记录在course中无时，那么只有student受影响，如果sc表中的记录，在student中没有记录时，那么两者都是0行受影响)


create trigger del_course
on course
instead of delete
as
begin
print '你不能删除该表的任何记录'
end

DELETE
FRom course
where cno='1'