触发器实验 3

实验（一）：MS SQL Server的使用和管理
一、实验目的
①掌握T-SQL语言中几个常用流程控制语句的使用。

②通过对常用系统存储过程的使用，了解存储过程的类型。

③通过创建和执行存储过程，了解存储过程的基本概念，掌握使用存储过程的操作技巧和方法。

二、实验内容
①用两种方法（if…else语句与while、break和continue语句）实现1+2+3……+100值的计算。

②活期存款中，“储户”通过‘存取款单’和“存储所”发生关系。

假定储户包括：账号，姓名，电话，地址，存款额；“储蓄所”包括：储蓄所编号，名称，电话，地址。

假定一个储户可以在不同的储蓄所存取款，试完成以下设计：
1．创建一个数据库名为SA VE，按要求在SA VE数据库中创建以上的那三个表“储户”、“存取款单”、“储蓄所”。

在三张表中自己选择应该在哪些列创建主键外键约束。

2．为“存取款单”表创建一个CHECK约束，使存取标志列的值要么是1要么是0。

3．创建一个触发器TR1完成下面的内容：
当向“存取名单”表中插入数据时，如果存取标志=1则应该更正储户表让存款额加上存取金额，如果存取标志=0则应该更正储户表让存款额减去存取金额，如果余额不足则显示余额不足错误。

4．创建一个视图显示用户账号、用户姓名、存款额和所在银行。

5．创建存储过程INPUT_PROC,OUT_PROC分别用于存款和取款，即向存取款单中插入数据。

三、实验步骤
1．启动sql查询分析器，运行相关sql脚本。

2．脚本文件如下：
创建一个数据库名为SA VE：
CREATE DATABASE[SA VE]
ON PRIMARY
(NAME=lfm1_dat,
FILLENAME='c:\SAVE.DA TA.mdf',
SIZE=10,
MAXSIZE=50,
FILEGROWTH=15%)
LOG ON
(NAME='lfm_log',
FILENAME='c:\SAVELOG.ldf',
SIZE=5MB,
MAXSIZE=25MB,
FILEGROWTH=5MB)
创建以上的那三个表“储户”、“存取款单”、“储蓄所”，在三张表中自己选择应该在哪些列创建主键外键约束。

CREATE TABLE 储户
(账号INT PRIMARY KEY,姓名CHAR(10),电话INT,地址CHAR(10),存款额MONEY)
GO
CREATE TABLE 储蓄所
(
储蓄所编号INT PRIMARY KEY，名称CHAR(10),电话INT，地址CHAR(10)
)
GO
CREATE TABLE 存取款单
(
账号INT NOT NULL，储蓄所编号INT NOT NULL，存取日期DA TETIME NOT NULL，存取标志INT NOT NULL，存取金额MONEY
)
GO
为“存取款单”创建主键约束。

ALTER TABLE 存取款单
ADD CONSTRAINT PK
PRIMARY KEY(账号,储蓄所编号,存取日期)
GO
一个储户要存钱，实际上就是向存取款单中插入数据。

银行的商业规则规定，一个人必须开了账户才能存取钱，即“储户”表中存在要存取款人的帐号。

并且这个人必须到储蓄所去存取钱，即你要插入到“存取款单”表的储蓄所编号列必须在“储蓄所”中存在。

要实现以上商业规则，创建以下两个外键约束。

ALTER TABLE 存取款单
ADD CONSTRAINT FK2
FOREIGN KEY(账号)
REFERENCES 储户(账号)
GO
ALTER TABLE 存取款单
ADD CONSTRAINT FK1
FOREIGN KEY(储蓄所编号)
PEFERENCES 储蓄所(储蓄所编号)
GO
3．为“存取款单”表创建一个CHECK约束，使存取标志列的值要么是1要么是0。

ALTER TABLE 存取款单
ADD CONSTRAINT CH
CHECT(存取标志=1 OR 存取标志=0)
GO
4．创建一个触发器TR1完成当向“存取款单”表中插入数据时，如果存取标志=1，则应该更改存储表让存款额加上存取金额，如果存取标志=0则应该更改储户表让存款额减去存取金额，如果余额不足显示余额不足错误。

CREATE TRIGGER TR1
ON 存取款单
FOR INSERT
AS
DECLARE @BZ INT,@MONEY MONEY,@ZH INT
SELECT @BZ=存取标志，@MONEY=存取金额,@ZH=账号
FROM INSERTED
IF @BZ=0
BEGIN
UPDATE 储户
SET 存款额=存款额-@MONEY
WHERE 账号=@ZH
DECLARE @SY MONEY
SELECT @SY=存款额FROM 储户
WHERE 账号=@ZH
IF @sy<0
BEGIN
RAISERROR('余额不足'16,1)
ROLLBACK
END
END
IF @BZ=1
BEGIN
UPDATE 储户
SET 存款额=存款额+@MONEY
WHERE 账号=@ZH
END
5．创建一个视图显示用户账号、用户姓名、存款额和所在银行。

CREATE VIEW V1
AS
SELECT 储户.账号,姓名,存款额,名称AS 储蓄所名称
RROM 储户INNER JOIN 存取款单
ON 储户.账号=存取款单.账号
INNER JOIN 储蓄所
ON 储蓄所.储蓄所编号=存取款单.储蓄所编号
6．创建存储过程INPUT_PROC,OUTPUT_PROC分别用于存款和取款，即向存取款单中插入数据。

CREATE PROC OUT_PROC
@ZH INT,@CXSBH INT,@CCJE MONEY
AS
INSERT 存取款单(账号,储蓄所编号,存取日期,存取标志,存取金额)
V ALUES
(@ZH,@CXSBH,GETDATE(),0,@CCJE)
GO
CREATE PROC INTUT_PROC
@ZH INT,@CXSBH INT,@CCJE MONEY
AS
INSERT 存取款单(账号,储蓄所编号,存取日期,存取标志,存取金额)
V ALUES
(@ZH,@CXSBH,GETDATE(),1,@CCJE)。