触发器实验 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