数据库实验九存储过程和触发器班.doc

实验09存储过程和触发器 〖仪器设备〗 每位学生一台PC机（标准配制，1G内存），装有SQL Server 2005或者其他DBMS 〖目的要求〗 1.了解几个常用的系统存储过程。 2.掌握如何使用T-SQL语句创建、执行和重编译存储过程。 3.了解如何使用企业管理器管理存储过程。 4.掌握如何使用T-SQL语句创建触发器。 5.了解如何使用企业管理器创建触发器。 6.掌握AFTER触发器的用法，并了解INSTEAD OF触发器的用法。 〖实验内容〗 1.创建存储过程。 (1)创建一个存储过程，查看“2”号课程的选修情况，包括选修该课程的学号、姓名和成绩。 执行存储过程 2.使用输入参数 (1)上面所建立的存储过程只能对“2”号课程的选修情况进行查看，要想对所有课程进行随机查看，需要进行参数的传递。 (2)按位置传递参数 (3)通过参数名传递参数 3.使用默认参数值 (1)执行存储过程ssc_4时，如果没有给出参数，系统会报错。如果希望不给参数时，能查询所有课程的进修情况，则可以使用默认参数值来实现。 (2)执行下面两条语句，比较执行结果。 4.使用输出参数。 (1)创建一个存储过程ssc_6，获得选修某门课程的总人数。 (2)执行存储过程ssc_6。 5.使用返回值。 (1)创建一个返回执行状态码的存储过程ssc_7，它接受课程号为输入参数，如果执行成功，返回0；如果没有给出课程号，返回错误码1；如果给出的课程号不存在，返回错误码2；如果出现其他错误，返回错误码3。 执行存储过程ssc_7。 6.修改存储过程-使用对象资源管理器修改存储过程。 查看存储过程。 删除存储过程。 9.创建触发器。 (1)在表student中建立删除触发器，实现表student和表sc中数据的级联删除。 (2)在对象资源管理器中，向表course中插入一列status(char(1))，并且默认值为“0”。在表SC上建立一个Insert触发器，当向表SC中插入一行时，检查课程表中的课程是否正在准备中（查看对应课程在Course中的状态是否为1），如果是在准备中，则不能进行选修。 (3)运行以上创建触发器的代码，然后再向表course中插入以下记录。 (4)向SC表加入“9”号课程的选修记录，查看执行情况。 (5)将“9”号课程的状态必为“0”。向表SC加入“9”号课程的选修记录，查看执行情况。 (6)表SC的cno列设置了外键约束，试用触发器实现这一功能。 (7)在上例中，只针对插入单行数据，不包括对多行的判断，如果要利用触发器对插入的数据进行逐行检查，并将那些课程号在表Course中不存在的行删掉，请修改以上触发器。 10.使触发器无效或重新有效。 (1)使表SC上的scinsert触发器无效。 (2)使表SC上的scinsert触发器重新有效。 11.删除scinsert触发器。 〖问题解答〗 1.创建存储过程之前应考虑什么？ 创建存储过程之前应考虑以下4点： ? CREATE PROCEDURE语句不能与别的T-SQL语句位于同一个批中； ? 创建存储过程的权限默认属于数据库拥有者，该权限可以授予他人； ? 存储过程是数据库对象，它的命名应符合标识符的命名规范； ? 只能在当前数据库中创建存储过程。 2.什么是INSERTED表和DELETED表？ SQL Server为每个触发器都创建了两个专用表：INSERTED表和DELETED表。这是两个逻辑表，由系统来维护，用户不能对它们进行修改。它们存放在内存而不是数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后，与该触发器相关的这两个表也被删除。 DELETED表存放由于执行DELETE或UPDATE语句而要从表中删除的所有行。在执行DELETE或UPDATE操作时，被删除的行从激活触发器的表中被移动到DELETED表，这两个表不会有共同的行。 INSERTED表存放由于执行INSERT或UPDATE语句而要向表中插入的所有行。在执行INSERT或UPDATE操作时，新的行同时添加到激活触发器的表和INSERTED表中，INSERTED表的内容是激活触发器的表中新行的备份。 〖思考、练习题〗 使用对象资源管理器创建存储过程ssc_3。 使用对象资源管理器查看存储过程ssc_3的定义。 3.使用对象资源管理器删除存储过程ssc_3。 4.在创建触发器之前要考虑哪些问题？ ? CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象。 如果指定触发器