SQLServer2008数据库实用技术参考例题（9）存储过程和触发器

　　存储过程和触发器例12-1
　　/*例12-1简单存储过程举例，查询不及格课程超过4门的学生并将其信息存入退学表*/
　　CREATE PROC Query_Proc
　　AS
　　INSERT INTO tuixue
　　SELECT*
　　FROM stu_info
　　WHERE stu_id IN (SELECT stu_id
　　FROM stu_grade
　　WHERE grade <60
　　GROUP BY stu_id
　　HAVING COUNT(*)>4
　　);
　　例12-2
　　/*12-2带参数从存储过程举例，输入学号和课程号，输出成绩*/
　　CREATE PROC Query_Grade
　　(
　　@stu_id VARCHAR(10),
　　@course_name VARCHAR(20),
　　@grade INT OUTPUT
　　)
　　AS
　　SELECT @grade=grade
　　FROM stu_grade,course_info
　　WHERE stu_grade.course_id=course_info.course_id
　　AND stu_id=@stu_id AND course_name=@course_name
　　例12-3
　　/*例12-3修改存储过程示例，将存储过程Query_Proc修改为查询某个学生不及格的课程数*/
　　ALTER PROC Query_Proc
　　(
　　@stu_id VARCHAR(10),
　　@num INT OUTPUT
　　)
　　AS
　　SELECT @num=COUNT(*)
　　FROM stu_grade
　　WHERE grade
　　例12-4
　　/*例12-4执行存储过程Query_Proc*/
　　EXEC Query_Proc 例12-5
　　/*例12-5按顺序给参数赋值，执行存储过程例Query_Grade,查询2007070201的操作系统这门课的成绩*/
　　DECLARE @score INT
　　EXEC Query_Grade＂2007070201＂,＂操作系统＂,@score OUTPUT
　　SELECT @score
　　例12-6
　　/*12-6按名称对参数赋值*/
　　DECLARE @score INT
　　EXEC Query_Grade @stu_id=＂2007070101＂,
　　@course_name=＂操作系统＂,
　　@grade=@score OUTPUT
　　SELECT @score
　　例12-7
　　/*例12-7使用输出参数返回数据，返回某个系的学生个数*/
　　CREATE PROC Count_Proc_1
　　(
　　@dept_name VARCHAR(20),
　　@num INT OUTPUT
　　)
　　AS
　　SELECT @num=count(*)
　　FROM stu_info
　　WHERE sdept=@dept_name 例12-8
　　/*例12-8使用Return语句返回执行状态*/
　　CREATE PROC Count_Proc_2
　　(
　　@dept_name VARCHAR(20),
　　@num INT OUTPUT
　　)
　　AS
　　IF @dept_name=＂＂
　　BEGIN
　　PRINT＂请输入系名！＂
　　RETURN 1
　　END
　　SELECT @num=count(*)
　　FROM stu_info
　　WHERE sdept=@dept_name
　　IF @num=0
　　BEGIN
　　PRINT ＂系名不正确，或该系目前没有学生＂
　　RETURN 2
　　END
　　RETURN 0
　　例12-9
　　/*例12-9 INSERT 触发器示例。在课程表中插入新的课程时，检查插入的记录是否满足参照完整性*/
　　CREATE TRIGGER insert_course ON course_info
　　FOR INSERT
　　AS
　　DECLARE @pre_course_id CHAR(3)
　　SELECT @pre_course_id =pre_course_id FROM inserted
　　IF @pre_course_id not IN (SELECT course_id FROM course_info)
　　BEGIN
　　RAISERROR(＂输入的先行课程不存在！＂,16,10)
　　ROLLBACK TRAN
　　END
　　例12-10
　　/*例12-10创建一个Update触发器，防止用户修改学生的入学成绩*/
　　CREATE TRIGGER update_stu on stu_info
　　FOR UPDATE
　　AS
　　IF UPDATE(mark)
　　BEGIN
　　RAISERROR (＂不能修改入学成绩！＂,16,10)
　　ROLLBACK TRAN
　　END
　　例12-11
　　/*例12-11创建一个delete触发器，将删除的记录存入一个备份表*/
　　CREATE TRIGGER delete_stu on stu_info
　　FOR DELETE
　　AS
　　INSERT INTO stu_backup
　　SELECT *FROM deleted
　　例12-12
　　/*例12-12 创建Update触发器实现级联修改*/
　　CREATE TRIGGER update_course ON course_info
　　FOR UPDATE
　　AS
　　DECLARE @new_id CHAR(3),@old_id CHAR(3)
　　SELECT @new_id =course_id FROM inserted
　　SELECT @old_id =course_id FROM deleted
　　IF UPDATE(course_id)
　　UPDATE course_info
　　SET pre_course_id=@new_id
　　WHERE pre_course_id=@old_id
　　例12-13
　　/*例12-13设计DDL触发器，紧张修改和删除当前数据库中的任何表*/
　　CREATE TRIGGER safety
　　ON DATABASE
　　FOR DROP_TABLE,ALTER_TABLE
　　AS
　　PRINT ＂不能修改和删除表！＂
　　ROLLBACK
　　;
　　例12-14
　　/*例12-14 创建登录触发器如果某登录帐号已经创建5次，则禁止该登录帐号的登录尝试*/
　　CREATE TRIGGER connect_limit_trigger5
　　ON ALL Server
　　FOR Logon
　　AS
　　BEGIN
　　IF (SELECT count(*) FROM sys.dm_exec_sessions
　　WHERE is_user_process =1 AND original_login_name=ORIGINAL_LOGIN())>5
　　ROLLBACK
　　END
　　; 例12-15
　　/*例12-15删除触发器insert_course*/
　　DROP TRIGGER insert_course
　　例12-16
　　/*例12-16删除DDL触发器safety*/
　　DROP TRIGGER safety ON DATABASE
　　例12-17
　　/*例12-17删除登录触发器connection_limit_trigger*/
　　DROP TRIGGER connect_limit_trigger ON ALL SERVER