文档库

最新最全的文档下载
当前位置:文档库 > 数据库实验6触发器实验报告

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

淮海工学院计算机工程学院实验报告书

课程名:《数据库原理及应用》

题目:存储过程和触发器

班级:软件132

学号:2013122907

姓名:孙莹莹

评语:

成绩:指导教师:

批阅时间:年月日

一.目的与要求

1.掌握存储过程的创建方法;

2.掌握存储过程的执行、修改和删除等操作;

3.掌握触发器的创建方法;

4.掌握触发器的使用、修改和删除等相关内容

二.实验内容

基于前面建立的factory数据库,使用T-SQL语句完成如下各小题的功能:

1.创建一个为worker表添加职工记录的存储过程Addworker;

2.创建一个存储过程Delworker删除worker表中指定职工号的记录。

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

@birthday 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 @@error<>0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRANSACTION

PRINT '职工'+@name+'的信息成功添加到表worker中'

(2)验证存储过程

USE factory

GO

Addworker 20,'陈立','女','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 @@error<>0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRANSACTION

PRINT '成功删除职工号为'+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 name 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 int,@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

WHERE 部门号=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 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 TRIGGER worker_delete

GO

四.测试数据与实验结果

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

第1题图

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

第2题图

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

第3题图

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

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

第5题图

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

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

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

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

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

第6题图

五.结果分析与实验体会

CREATE PROCEDURE语句允许创建、编译并在MS-SQL Server上保存存储过程。在默认情况下,只有数据库拥有者(DBO)具有对数据库的CREATE PROCEDURE访问权。但是,DBO可执行以下形式的GRANT语句向由语句中的所标识的用户ID授予CREATE PROCEDURE 访问权:

GRANT CREATE PROCEDURE

CREATE PROCEDURE语句的句法如下:

CREATE PROCEDURE [;]

[{@ [V ARYING]

[=] [OUTPUT]

[…,n]}]

[WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FOR REPLICATION]

AS

其中:

是存储过程的名称,名称至多可有128个字符。

允许创建多个有相同名称的存储过程。通过在执行命令中包括版本号从而执行指定版本的存储过程,如下面的语句所示:EXEC usp_proc;2。如果调用存储过程时没有指定版本号,DBMS将执行具有相同名称的存储过程组中最高版本号的存储过程。通过在DROP语句中包括版本号,如DROP PROCEDURE usp_proc;2所示,可以删除特定版本号的存储过程,或者忽略版本号,如DROP PROCEDURE usp_proc从而一次删除存储过程的所有版本。

是可用作存储过程中的变量的参数名。每个参数的值必须在存储过程调用中提供或者作为CREATE PROCEDURE语句的一部分设置为缺省值。虽然参数可用作存储过程中的变量名,但参数不能用作列名、表名或是其他数据库对象的名称。

是参数的数据类型。参数可以是任何合法的SQL数据类型(包括TEXT、NTEXT 和IMAGE)或用户定义的数据类型。如果参数是CURSOR数据类型,该参数还必须被指定为V ARYING和OUTPUT。

V ARYING仅对数据类型为CURSOR的参数才是合法的。指明该参数将包括内容会变化的结果集,其内容由存储过程中的语句动态地构成。

是参数的默认值。如果指定,则过程可在不指定参数的情况下执行。

OUTPUT指明参数在存储过程中可以变化,而且修改后的值可返回给主调过程。

…,n指明CREATE PROCEDURE语句可有多达2100个参数。

RECOMPILE每当存储过程被调用时告诉MS-SQL Server都要进行编译(也就是生成新的执行计划)。如果没有此选项项,DBMS在执行CREATE PROCEDURE语句时编码存储过程,每次调用时都使用同一执行计划。

ENCRYPTION告诉MS-SQL Server加密SYSCOMMENTS表中存储过程条目的文本,以防止用户查看编译后的存储过程中的语句。指定此选项还可防止存储过程被除数作为MS-SQL Server的复制过程而分开。

由于创建的触发器在条件成立时会自动被调用,可能影响后面示例的执行,所以当一个触发器不再需要时,需将其禁用,禁用trigop触发器的操作是:选中factory表节点,展开下方的触发器节点,右击trigop,在出现的快捷菜单中选择禁用命令。如要重新启动已禁用的触发器,在这里选择启用命令即可启动该触发器。

SQL Server2005中创建的DML触发器有两种:旧类型的触发器和INSTEAD OF触发器。旧类型触发器现在叫AFTER触发器,这种类型的触发器在INSERT、UPDATE或DELETE语句执行后才会触发执行,并且只能定义在表上。当用户创建旧类型触发器时,应使用新关键字(AFTER),出于兼容性考虑,旧的关键字(FOR)任然能够使用,但是不推荐再使用它。