数据库实验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
四．测试数据与实验结果
第1题图
第2题图
第3题图
第5题图
第6题图
五．结果分析与实验体会
CREATE PROCEDURE语句允许创建、编译并在MS-SQL Server上保存存储过程。在默认情况下，只有数据库拥有者（DBO）具有对数据库的CREATE PROCEDURE访问权。但是，DBO可执行以下形式的GRANT语句向由语句中的
CREATE PROCEDURE语句的句法如下：
CREATE PROCEDURE
[{
[=
[…,n]}]
[WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS
其中：
提供或者作为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，在出现的快捷菜单中选择禁用命令。如要重新启动已禁用的触发器，在这里选择启用命令即可启动该触发器。
SQL Server2005中创建的DML触发器有两种：旧类型的触发器和INSTEAD OF触发器。旧类型触发器现在叫AFTER触发器，这种类型的触发器在INSERT、UPDATE或DELETE语句执行后才会触发执行，并且只能定义在表上。当用户创建旧类型触发器时，应使用新关键字（AFTER），出于兼容性考虑，旧的关键字（FOR）任然能够使用，但是不推荐再使用它。