创建存储过程与触发器

实验7 创建存储过程与触发器

实验日期和时间：2011-11-11 实验室：2#206

班级：09计本（4）学号：2009810182 姓名：周伟

实验环境：

1.硬件：1G内存 1.73GHz

2.软件：SQL server2008

实验原理：

创建存储过程，执行存储

通过建立触发器实现对数据库的更新。

实验任务：

此作业成绩得分根据你完成的任务的难度和数量评分，完成后在实验室给老师演示验收，课后提交电子版报告。如额外完成自拟题目应当事先将所拟题目提交给老师或在报告中明确标注题意。

假定有某个企业（或公司，或代理商）经销某类产品，需要用一个信息系统对销售业务和库存进行管理。

首先，他们得在数据库中存储所有经营过的产品的信息，并建立现有库存的信息表；

其次，对于每次销售或者进货，他们都得记录下来以便进行管理，将来对这些信息进行统计或财务管理；再次，在每笔销售记录中需要记录相关客户信息，在进货时需要记录相关供应商的信息，也为了与不同的供应商和客户进行联系，需要分别建立二者的信息表。综上所述，在以上建立的数据库中我们至少需要以下几个基本表：

1.产品表（记录公司曾经经营的所有产品信息）

2.现有库存表（记录公司目前经营的产品的现有库存信息）

3.出库单表（记录产品销售出库时的情况：时间、销售员、客户、商品编码、

商品数量等）

4.入库单表（记录公司每次产品进货入库时的信息）

5.供应商表（记录为公司供货的主要供应商信息）

6.客户表（记录公司的所有客户信息）

以下是供参考的表结构的部分信息，同学们可以根据题意自行修改表的结构

1.产品表（记录公司的产品信息）

字段名数据类型长度备注

产品编号文本主键

产品名称文本非空

类别文本

供应商编号文本外键（来自供应商表）

产地文本

最新参考单价货币

规格文本

……

……

2.现有库存表（记录公司的现有库存信息）

字段名数据类型长度备注

产品编号文本主键、外键（来自产品表）产品名称

产品规格

类别

零售单价货币

……

现有库存量数字

最小库存量数字

存放地点文本

……

3.出库单表（记录产品销售出库时的情况）

字段名数据类型长度备注

出库单号文本主键

客户编号文本外键（来自客户表）

产品编号文本外键（来自产品表）

出库数量数字

出库价格货币

金额货币＝出库数量×出库价格……

出库日期日期/时间可以默认为系统时间

目的地文本

经手人文本

4.入库单表（记录公司每次产品进货入库时的信息）

字段名数据类型长度备注入库单号文本主键

产品编号文本外键（来自产品表）

供应商编号文本外键（来自供应商表）入库数量数字

入库价格货币

……

入库日期日期/时间可以默认为系统时间

经手人文本

5.供应商表（记录为公司供货的主要供应商信息）

字段名数据类型长度备注

供应商编号文本主键

供应商名称文本非空

联系人姓名文本

地址文本

……

电话文本

传真文本

电子邮箱文本

6.客户表（记录公司的所有客户信息）

字段名数据类型长度备注

客户编号文本主键

客户名称文本非空

联系人文本

城市文本

地址文本

……

电话文本

传真文本

电子邮箱文本

要求：

1)设计并创建以上的“库存管理”系统的数据库。注意，建表时，表中的外键的数据类型应当与其所参照的主表中的主键数据类型一致。（至少创建题目所需要的表）

2)创建关系图，建立表之间的联系以保证参照完整性。

3)基本数据录入。可以直接录入，也可将其它格式的表中的数据导入，或查询其它表中可利用的数据并插入到现有的表中。

4)创建存储过程。（任选一题）

①创建可以按“产品编号”（参数）进行产品库存信息查询的存储过程。

②创建可以按“类别”（参数）进行某类产品库存信息查询的存储过程。

③创建可以按产品名称或产品名称打头字符串（参数）进行产品库存信息查询的存储过程。

④自拟题

5)创建触发器。（任选一题）

①创建“现有库存”表的DELETE触发器，禁止删除库存信息。（只需要现有库存表）

②创建“出库单”表的INSERT触发器。在该表中插入出库记录时，能自动生成唯一的出库单号（可设置为自动编号），在填写“产品编号”和“出库数量”时，通过触发器的作用，能判断该产品的现有库存数量是否足够，如果足够满足此次出库数量，则能自动填写出库记录中与该记录“产品编号”对应的：

“产品名称”（来自产品表/现有库存表）、

“产品规格”（来自产品表/现有库存表）、

“出库价格”（来自产品表/现有库存表）、

“金额”（能自动计算并填入：=出库价格*出库数量）、

“出货日期”（来自系统日期）等字段，

并能根据此次出库数量自动减少该产品的现有库存数量值；

如果现有库存数量不能满足此次出库数量，则拒绝此记录插入（事务回滚）并报警提示库存不足。（需要现有库存表和出库单表）

③创建“出库单”表的INSERT触发器。如果此产品出库后的现有库存量低于最小库存量，则报警提示该产品库存不足需要进货。（需要现有库存表和出库单表）

④创建“入库单”表的INSERT触发器。在该表中插入入库记录时，能自动生成唯一的入库单号（可设置为自动编号），在填写“产品编号”和“入库数量”时，通过触发器的作用，能判断在“现有库表中”是否存在该产品库存记录，如果有，则自动更新该产品的现有库存数量，如果现有库存表中不存在该产品的库存记录（有可能是未经营过的新产品），则先在现有库存表中自动插入该产品的库记录；并通过触发器的作用自动填写入库记录中与该记录“产品编号”对应的：

“产品名称”（来自产品表/现有库存表）、

“产品规格”（来自产品表/现有库存表）、

“入库价格”（来自产品表的最新参考单价/现有库存表的零售单价）、

“金额”（能自动计算并填入：=出库价格*出库数量）、

“出货日期”（来自系统日期）等字段。

（需要现有库存表和入库单表）

先在第一栏填写自己选择的题目和欲实现的功能，再在其余栏目中分别填写自己的代码以及执行情况、测试方案和数据、测试结果等等。如果选做多个或自拟题，请自己依照格式添加栏目，自拟题请写清题意。

我的选题1：（描述题目和欲实现的功能）

1.创建可以按“产品编号”（参数）进行产品库存信息查询的存储过程。

原代码：

创建存储过程

create procedure xinxi_cx@123nchar(10)

as

begin

select*from现有库存量

where产品编号=@123

end;

测试方案及数据：

在新建查询中输入：EXEC@result = [dbo].[xinxi_cx]

@123 = N'001'

观察能否返回产品编号为001的产品现有库存信息

测试结果：(文字说明、原代码、结果贴图)

2．创建可以按“类别”（参数）进行某类产品库存信息查询的存储过程

原代码：

创建存储过程: create procedure chanpinleibie@123nchar(10) as

begin

select*from产品表

where类别=@123

end;

测试方案及数据：

在新建查询中输入:exec@result=chanpinleibie

@123='电'

select'result'=@result

测试结果：(文字说明、原代码、结果贴图)

3.创建可以按产品名称或产品名称打头字符串（参数）进行产品库存信息查询的存储

过程。

创建存储过程: create procedure chanpinmingcheng@123nchar(10)

as

begin

select*from产品表

where产品名称=@123

end;

在新建查询中输入:exec@result=chanpinmingcheng

@123='电'

select'result'=@result

测试结果：(文字说明、原代码、结果贴图)

4.创建可以按“供应商编号”（参数）进行产品库存信息查询的存储过程。

创建存储过程: create procedure gongyingshang@123nchar(10)

as

begin

select*from产品表

where供应商编号=@123

end;

在新建查询中输入:exec@result=gongyingshang

@123='102'

select'result'=@result

测试结果：(文字说明、原代码、结果贴图)

我的选题2：（描述题目和欲实现的功能）

1.创建“现有库存”表的DELETE触发器，禁止删除库存信息

原代码：

create trigger库存_信息删除on现有库存量

for delete

as

declare@123nchar(10)

begin

IF EXISTS(SELECT*FROM现有库存量WHERE产品编号=@123) PRINT'禁止删除库存信息！'

ROLLBACK;

ROLLBACK TRANSACTION

END;

use zw

go

delete

from现有库存量where产品编号='004'

go

测试方案及数据：

use zw

go

delete from现有库存量where产品编号='004'

Go

测试结果：(文字说明、原代码、结果贴图)

2.创建“出库单”表的INSERT触发器。

原代码：

create trigger插入出库单表时更新

on出库单表

for insert

as

declare@zdbh int,@khbh nchar(10),@cpbh nchar(10),@cksl numeric,@ckjg money,@je money,@ckrq datetime,@mdd nchar(10),@jsr nchar(10),@xykcl numeric

begin

select@zdbh,@cpbh

where exists(select现有库存量from现有库存量

where@xykcl=@cksl)

insert into出库单表

values(@zdbh,@khbh,@cpbh,@cksl,@ckjg,@je,@ckrq,@mdd,@jsr);

end;

测试结果：(文字说明、原代码、结果贴图)

③创建“出库单”表的INSERT触发器

源代码：create trigger插入出库单表

on出库单表

for insert

as

declare@zdbh int,@khbh nchar(10),@cpbh nchar(10),@cksl numeric,@ckjg money,@je money,@ckrq datetime,@mdd nchar(10),@jsr nchar(10),@xykcl numeric

begin

if exists(select*from现有库存量

where现有库存量<@cksl)

print'库存不足需要补货!'

ROLLBACK;

ROLLBACK TRANSACTION

end;

测试结果：

4.创建“入库单”表的INSERT触发器。

原代码：

create trigger插入入库单表

on入库单表

for insert

as

declare@zdbh int,@cpbh nchar(10),@gysbh nchar(10),@rksl numeric,@rkjg money,@rkrq datetime,@jsr nchar(10),@xykcl numeric,@cksl nchar

begin

select@zdbh,@cpbh

where exists(select现有库存量from现有库存量

where@xykcl=@cksl)

insert into入库单表values(@zdbh,@cpbh,@gysbh,@rksl,@rkjg,@rkrq,@jsr)

end;

结果：

本实验总结：（不少于100字）

书上介绍的内容根本不够啊

所以就上网查找一些例题研究才基本上弄懂了

以后得多看看书本以外的知识增加理解的范围

要求：

1.报告格式和内容要求：

a. 内容和格式整齐。大标题采用黑体四号字加粗，小标题采用小四号字加粗。正

文采用五号宋体，单倍行距。

b. 贴图时请剪裁到适当大小，要保证打印时可以看清，但也不要太大以免“越界”。

c. 不要在报告中写与实验无关的话，内容要有条理、完整、并能突出重点，要将

遇到的主要问题说明。

2.文件格式要求：

a. 将实验成果放入一个文件夹中，文件夹的内容包括：本实验报告、分离后的数

据库、如有查询代码（.sql文件）和其它文件也一并放入。

b. 文件夹以“学号姓名班级_S7”为文件名。

c.所有文件保存后关闭，然后再打包成RAR文件，以免提交的内容丢失或打不开。

3.提交方式和时间：下周必须提交。将文件包发到我邮箱bbwhs@https://www.360docs.net/doc/4f11787773.html,。

4.主动查阅资料，坚持自己亲手完成实验，弄清每个步骤和相关原理。