SQL触发器使用参考代码.doc

 PAGE ５ 触发器 use EBuy if exists (select * from sys.all_objects where name=custDel and type=TR) --判断custDel存储过程是否存在，存在则删除它 drop trigger custDel go create trigger custDel on customer --创建存储过程 for delete --触发器类型 as print 客户记录被删除 delete from customer where cusid=1201 --删除验证 select * from customer --查询customer表 sp_helptext custDel --查看存储过程 use Study --创建新表 create table major_stat( majorName varchar(50), toStu int, toCredit int ) create trigger updateMajorStat --创建存储过程 on student for insert,update,delete --触发器类型 as begin delete from major_stat insert into major_stat select majorName,count(*), sum(credit) from student s inner join major m on s.majorid=m.majorid group by majorName end insert into student values(071126,李静,2,男,1985-10-20,42,null) update student set credit=credit+2 where majorid=1 delete from student where stuid=071126 select * from student select * from major_stat --insert触发器 use Study go select * from course select * from student_course select * from student if exists(select name from sys.all_objects where name=insertScore and type=TR) drop trigger insertScore go create trigger insertScore on student_course for insert as if(select score from inserted)=60 begin declare @stuid char(6),@couid char(3),@credit int select @stuid=stuid,@couid=couid from inserted select @credit=couCredit from course where couid=@couid update student set credit=credit+@credit where stuid=@stuid end insert into student_course values(071001,C17,70) --update触发器 if exists(select name from sys.all_objects where name=updateCouHour and type=TR) drop trigger insertScore go create trigger updateCouHour on course for update as declare @oldnum int,@newnum int begin select @oldnum=couHour from deleted select @newnum=couHour from inserted if @newnum@oldnum or @newnum50 begin print 课时不能高于原课时数并且不能低于50 rollback tran end end go update course set couHour=couHour-2 where couid like A01 --delete触发器 create trigger delMajor on m