存储过程和触发器

例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<60 AND stu_id=@stu_id

例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