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

1、数据库实验数据库实验 6 触发器实验报告触发器实验报告 淮海工学院计算机工程学院 实验报告书 课程名：数据库原理及应用 题 目：存储过程和触发器 班 级：软件 132 学 号：2013122907 姓 名：孙莹莹 一 目的与要求 1.掌握存储过程的创建方法；2.掌握存储过程的执行、修改和删除等操作；3.掌握触发器的创建方法；4.掌握触发器的使用、修改和删除等相关内容 二 实验内容 基于前面建立的 factory数据库，使用 T-SQL 语句完成如下各小题的功能：1.创建一个为 worker 表添加职工记录的存储过程 Addworker；2.创建一个存储过程 Delworker删除 worker

2、表中指定职工号的记录。3.显示存储过程 Delworker；4.删除存储过程 Addworker 和 Delworker.三 实验步骤 1.(1)建立存储过程 USE factory GO CREATE 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=NULL AS IF no IS NULL OR name IS NULL OR sex IS NULL OR birth

3、day IS NULL OR depno IS NULL BEGIN PRINT 请重新输入该职工信息！PRINT 你必须提供职工号、姓名、性别、出生日期、部门号 RETURN END BEGIN TRANSACTION INSERT INTO worker VALUES(no,name,sex,birthday,na,wtime,depno)IF error0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION PRINT 职工+name+的信息成功添加到表 worker中（2）验证存储过程 USE factory GO Addworker 2

4、0,陈立,女,55/03/08,否,75/10/10,4 GO SELECT 职工号,姓名,性别,党员否 FROM worker GO 2.(1)建立存储程序 USE factory GO CREATE PROCEDURE Delworker no int=NULL AS IF no IS NULL BEGIN PRINT 必须输入职工号！RETURN END BEGIN TRANSACTION DELETE FROM worker WHERE 职工号=no IF error0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION PRINT 成功

5、删除职工号为+CAST(no AS CHAR(2)+的职工记录 执行下列语句，可验证存储过程的正确性：USE factory GO Delworker 20 GO SELECT 职工号,姓名,性别,党员否 FROM worker GO 3.USE factory GO EXEC sp_helptext Delworker GO 4.USE factory GO IF EXISTS(SELECT name FROM sysobjects WHERE name=Addworker AND type=P)DROP PROCEDURE Addworker GO IF EXISTS(SELECT nam

6、e FROM sysobjects WHERE name=Delworker AND type=P)DROP PROCEDURE Delworker GO 5.建立触发器 depart_update 的程序如下：USE factory GO IF EXISTS(SELECT name FROM sysobjects WHERE type=TR AND name=depart_update)DROP TRIGGER depart_update GO CREATE TRIGGER depart_update ON depart AFTER UPDATE AS DECLARE olddepno in

7、t,newdepno int SELECT olddepno=部门号 FROM deleted SELECT newdepno=部门号 FROM inserted UPDATE worker SET 部门号=newdepno WHERE 部门号=olddepno GO 执行下列语句，可验证存储过程的正确性：USE factory GO PRINT 将部门号改为 UPDATE depart SET 部门号=105 WHERE 部门号=101 GO SELECT 职工号,姓名,部门号 FROM worker GO PRINT 将部门号改为 UPDATE depart SET 部门号=101 WHE

8、RE 部门号=105 GO SELECT 职工号,姓名,部门号 FROM worker GO 6.建立触发器 worker_delete的程序如下：USE factory GO IF EXISTS(SELECT name FROM sysobjects WHERE type=TR AND name=worker_delete)DROP TRIGGER worker_delete GO CREATE TRIGGER worker_delete ON worker FOR DELETE AS DECLARE no int SELECT no=职工号 FROM deleted DELETE FROM

9、 salary WHERE 职工号=no GO 执行下列语句，可验证存储过程的正确性：USE factory GO PRINT 删除前的工资记录 SELECT*FROM salary GO DELETE FROM worker WHERE 职工号=15 GO PRINT 删除职工号为的职工记录后的工资记录 SELECT*FROM salary GO 7.删除触发器 depart_update 的程序如下：USE factory GO DROP TRIGGER depart_update GO 8.删除触发器 worker_delete的程序如下：USE factory GO DROP TRIG

10、GER worker_delete GO 四 测试数据与实验结果 第 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

11、;VARYING=OUTPUT,n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS 其中：是存储过程的名称，名称至多可有 128个字符。允许创建多个有相同名称的存储过程。通过在执行命令中包括版本号从而执行指定版本的存储过程，如下面的语句所示：EXEC usp_proc;2。如果调用存储过程时没有指定版本号，DBMS 将执行具有相同名称的存储过程组中最高版本号的存储过程。通过在 DROP 语句中包括版本号，如 DROP PROCEDURE usp_proc;2 所示，可以删除特定版本号的存储过程，或者忽略版本号，

12、如 DROP PROCEDURE usp_proc从而一次删除存储过程的所有版本。是可用作存储过程中的变量的参数名。每个参数的值必须在存储过程调用中提供或者作为 CREATE PROCEDURE 语句的一部分设置为缺省值。虽然参数可用作存储过程中的变量名，但参数不能用作列名、表名或是其他数据库对象的名称。是参数的数据类型。参数可以是任何合法的 SQL数据类型（包括TEXT、NTEXT和 IMAGE）或用户定义的数据类型。如果参数是 CURSOR 数据类型，该参数还必须被指定为 VARYING和 OUTPUT。VARYING仅对数据类型为 CURSOR 的参数才是合法的。指明该参数将包括内容会变

13、化的结果集，其内容由存储过程中的语句动态地构成。是参数的默认值。如果指定，则过程可在不指定参数的情况下执行。OUTPUT指明参数在存储过程中可以变化，而且修改后的值可返回给主调过程。,n指明 CREATE PROCEDURE 语句可有多达 2100个参数。RECOMPILE 每当存储过程被调用时告诉 MS-SQL Server 都要进行编译（也就是生成新的执行计划）。如果没有此选项项，DBMS 在执行 CREATE PROCEDURE 语句时编码存储过程，每次调用时都使用同一执行计划。ENCRYPTION告诉 MS-SQL Server 加密 SYSCOMMENTS 表中存储过程条目的文本，以

14、防止用户查看编译后的存储过程中的语句。指定此选项还可防止存储过程被除数作为 MS-SQL Server 的复制过程而分开。由于创建的触发器在条件成立时会自动被调用，可能影响后面示例的执行，所以当一个触发器不再需要时，需将其禁用，禁用 trigop 触发器的操作是：选中 factory表节点，展开下方的触发器节点，右击 trigop，在出现的快捷菜单中选择禁用命令。如要重新启动已禁用的触发器，在这里选择启用命令即可启动该触发器。SQL Server2005 中创建的 DML触发器有两种：旧类型的触发器和 INSTEAD OF触发器。旧类型触发器现在叫 AFTER 触发器，这种类型的触发器在 INSERT、UPDATE或 DELETE 语句执行后才会触发执行，并且只能定义在表上。当用户创建旧类型触发器时，应使用新关键字（AFTER），出于兼容性考虑，旧的关键字（FOR）任然能够使用，但是不推荐再使用它。