数据库实验6触发器实验报告.doc

淮海工学院计算机工程学院实验报告书课程名： 数据库原理及应用 题 目： 存储过程和触发器 班 级： 软件132 学 号： 2013122907 姓 名： 孙莹莹 评语：成绩： 指导教师： 批阅时间： 年 月 日 数据库原理及应用 实验报告 - 8 一 目的与要求1. 掌握存储过程的创建方法；2. 掌握存储过程的执行、修改和删除等操作；3. 掌握触发器的创建方法；4. 掌握触发器的使用、修改和删除等相关内容二 实验内容基于前面建立的factory数据库，使用T-SQL语句完成如下各小题的功能：1. 创建一个为worker表添加职工记录的存储过程Addworker；2. 创建一个存储过程Delworker删除worker表中指定职工号的记录。3. 显示存储过程Delworker；4. 删除存储过程Addworker和Delworker.三 实验步骤1 .(1)建立存储过程USE factoryGOCREATE PROCEDURE Addworker no int=NULL, name char(10)=NULL, sex char(2)=NULL, birthday datetime=NULL, na char(2)=NULL, wtime datetime=NULL, depno int=NULLASIF no IS NULL OR name IS NULL OR sex IS NULL OR birthday IS NULL OR depno IS NULLBEGIN PRINT 请重新输入该职工信息！ PRINT 你必须提供职工号、姓名、性别、出生日期、部门号 RETURNENDBEGIN TRANSACTION INSERT INTO worker VALUES(no,name,sex,birthday,na,wtime,depno) IF error0 BEGIN ROLLBACK TRAN RETURN ENDCOMMIT TRANSACTIONPRINT 职工+name+的信息成功添加到表worker中（2）验证存储过程USE factoryGOAddworker 20,陈立,女,55/03/08,否,75/10/10,4GOSELECT 职工号,姓名,性别,党员否 FROM workerGO2.(1)建立存储程序USE factoryGOCREATE PROCEDURE Delworker no int=NULLASIF no IS NULLBEGIN PRINT 必须输入职工号！ RETURNENDBEGIN TRANSACTION DELETE FROM worker WHERE 职工号=no IF error0 BEGIN ROLLBACK TRAN RETURN ENDCOMMIT TRANSACTIONPRINT 成功删除职工号为+CAST(no AS CHAR(2)+的职工记录执行下列语句，可验证存储过程的正确性：USE factoryGODelworker 20GOSELECT 职工号,姓名,性别,党员否 FROM workerGO3.USE factoryGOEXEC sp_helptext DelworkerGO4.USE factoryGOIF EXISTS (SELECT name FROM sysobjects WHERE name = Addworker AND type =P) DROP PROCEDURE AddworkerGOIF EXISTS (SELECT name FROM sysobjects WHERE name = Delworker AND type =P) DROP PROCEDURE DelworkerGO5.建立触发器depart_update的程序如下：USE factoryGOIF EXISTS (SELECT name FROM sysobjects WHERE type=TR AND name=depart_update) DROP TRIGGER depart_updateGOCREATE TRIGGER depart_update ON depart AFTER UPDATE AS DECLARE olddepno int,newdepno int SELECT olddepno=部门号FROM deleted SELECT newdepno=部门号FROM insertedUPDATE worker SET 部门号=newdepnoWHERE 部门号=olddepnoGO执行下列语句，可验证存储过程的正确性：USE factoryGOPRINT 将部门号改为UPDATE departSET 部门号=105WHERE 部门号=101GOSELECT 职工号,姓名,部门号FROM workerGOPRINT 将部门号改为UPDATE departSET 部门号=101WHERE 部门号=105GOSELECT 职工号,姓名,部门号FROM workerGO6.建立触发器worker_delete的程序如下：USE factoryGOIF EXISTS (SELECT name FROM sysobjects WHERE type=TR AND name=worker_delete) DROP TRIGGER worker_deleteGOCREATE TRIGGER worker_delete ON worker FOR DELETE AS DECLARE no int SELECT no=职工号FROM deletedDELETE FROM salary WHERE 职工号=noGO执行下列语句，可验证存储过程的正确性：USE factoryGOPRINT 删除前的工资记录SELECT * FROM salaryGODELETE FROM workerWHERE 职工号=15GOPRINT 删除职工号为的职工记录后的工资记录SELECT * FROM salaryGO7.删除触发器depart_update的程序如下：USE factoryGODROP TRIGGER depart_updateGO8.删除触发器worker_delete的程序如下：USE factoryGODROP TRIGGER worker_deleteGO四 测试数据与实验结果第1题图第2题图第3题图 第5题图第6题图五结果分析与实验体会CREATE PROCEDURE语句允许创建、编译并在MS-SQL Server上保存存储过程。在默认情况下，只有数据库拥有者（DBO）具有对数据库的CREATE PROCEDURE访问权。但是，DBO可执行以下形式的GRANT语句向由语句中的所标识的用户ID授予CREATE PROCEDURE访问权： GRANT CREATE PROCEDURE CREATE PROCEDURE语句的句法如下： CREATE PROCEDURE ; VARYING= OUTPUT,nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONAS 其中：是存储过程的名称，名称至多可有128个字符。允许创建多个有相同名称的存储过程。通过在执行命令中包括版本号从而执行指定版本的存储过程，如下面的语句所示：EXEC usp_proc;2。如果调用存储过程时没有指定版本号，DBMS将执行具有相同名称的存储过程组中最高版本号的存储过程。通过在DROP语句中包括版本号，如DROP PROCEDURE usp_proc;2所示，可以删除特定版本号的存储过程，或者忽略版本号，如DROP PROCEDURE usp_proc从而一次删除存储过程的所有版本。是可用作存储过程中的变量的参数名。每个参数的值必须在存储过程调用中提供或者作为CREATE PROCEDURE语句的一部分设置为缺省值。虽然参数可用作存储过程中的变量名，但参数不能用作列名、表名或是其他数据库对象的名称。是参数的数据类型。参数可以是任何合法的SQL数据类型（包括TEXT、NTEXT和IMAGE）或用户定义的数据类型。如果参数是CURSOR数据类型，该参数还必须被指定为VARYING和OUTPUT。VARYING仅对数据类型为CURSOR的参数才是合法的。指明该参数将包括内容会变化的结果集，其内容由存储过程中的语句动态地构成。是参数的默认值。如果指定，则过程可在不指定参数的情况下执行。OUTPUT指明参数在存储过程中可以变化，而且修改后的值可返回给主调过程。,n指明CREATE PROCEDURE语句可有多达2100个参数。RECOMPILE每当存储过程被调用时告诉MS-SQL Server都要进行编译（也就是生成新的执行计划）。如果没有此选项项，DBMS在执行CREATE PROCEDURE语句时编码存储过程，每次调用时都使用同一执行计划。ENCRYPTION告诉MS-SQL Server加密SYSCOMMENTS表中存储过程条目的文本，以防止用户查看编译后的存储过程中的语句。指定此选项还可防止存储过程被除数作为MS-SQL Server的复制过程而分开。由于创建的触发器在条件成立时会自动被调用，可能影响后面示例的执行，所以当一个触发器不再需要时，需将其禁用，禁用trigop触发器的操作是：选中factory表节点，展开下方的触发器节点，右击trigop，在出现的快捷菜单中选择禁用命令。如要重新启动