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

触发器use EBuyif exists (select * from sys.all_objects where name=custDel and type=TR) -判断custDel存储过程是否存在，存在则删除它drop trigger custDelgocreate trigger custDel on customer -创建存储过程for delete -触发器类型asprint 客户记录被删除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 studentfor insert,update,delete -触发器类型asbegindelete from major_statinsert into major_stat select majorName,count(*), sum(credit) from student s inner join major m on s.majorid=m.majorid group by majorNameendinsert into student values(071126,李静,2,男,1985-10-20,42,null)update student set credit=credit+2 where majorid=1delete from student where stuid=071126select * from studentselect * from major_stat-insert触发器use Studygoselect * from courseselect * from student_courseselect * from studentif exists(select name from sys.all_objects where name=insertScore and type=TR)drop trigger insertScoregocreate trigger insertScore on student_coursefor insertasif(select score from inserted)=60begindeclare stuid char(6),couid char(3),credit intselect stuid=stuid,couid=couid from insertedselect credit=couCredit from course where couid=couidupdate student set credit=credit+credit where stuid=stuidendinsert into student_course values(071001,C17,70)-update触发器if exists(select name from sys.all_objects where name=updateCouHour and type=TR)drop trigger insertScoregocreate trigger updateCouHouron coursefor updateasdeclare oldnum int,newnum intbeginselect oldnum=couHour from deletedselect newnum=couHour from insertedif newnumoldnum or newnum50beginprint 课时不能高于原课时数并且不能低于50rollback tranendendgoupdate course set couHour=couHour-2 where couid like A01-delete触发器create trigger delMajoron majorfor delete asif(select majorId from deleted)=1beginprint 不能删除计算专业rollback tranendgoalter table studentdrop constraint fk_student_majorgoselect * from majordelete from major where majorId=2select * into quit_stu from student where 1=2 -创建一个与student表结构相同的表quit_stucreate trigger delStuBackup -创建存储过程(把从student表删除的记录备份到quit_stu表)on studentfor deleteasinsert into quit_stu select * from deletedgodelete from student_course where stuid=071001delete from student where stuid=071001select * from student-instead ofuse Studyif exists(select name from sys.all_objects where name=delStuBackup and type=TR)drop trigger insertScoregocreate trigger delStuBackup -创建存储过程(把从student表删除的记录备份到quit_stu表)on studentinstead of deleteasdeclare stuid char(6)begin tryselect stuid=stuId from deleteddelete from student_course where stuId=stuiddelete from student where stuId=stuidinsert into quit_stu select * from deletedcommit tranprint 操作成功end trybegin catchrollback tranend catchgoset nocount on -不显示影响行数的消息godelete from student where stuId=071011select * from studentselect * from student_courseselect * from quit_stu-在视图上创建存储过程create table employee(empid char(5) primary key,name varchar(20),age int)create table jobs(id int identity(1,1) primary key,empid char(5),job varchar(30),salary money,foreign key(empid) references employee(empid)create view emp_job -创建视图asselect e.empid,name,age,job,salary from employee e inner join jobs j on e.empid=j.empidgoinsert into emp_job values(33012,jane,26,打字员,2000) -通过视图操作多个表提示有错误create trigger ins_emp_job -在视图上创建存储过程on emp_jobinstead of insertasinsert into employee select empid,name,age from insertedinsert into jobs select empid,job,salary from insertedgoinsert into emp_job values(33012,jane,26,打字员,2000) -创建完存储过程后，可以操作多个表select * from emp_job-触发器的查看、修改和删除sp_helptext ins_emp_job -查看触发器alter trigger delStuBackup -修改触发器on studentfor deleteasprint 触发器已经修改drop trigger delStuBackup -删除触发器alter table studentdisable trigg