本文最初由security9968发布于security9968的csdn博客，禁止任何形式的剽窃行为
转载原创文章请注明，转载自：security的博客

实验9：存储过程实验

自拟题目完成8个存储过程的编写及调试，熟练掌握存储过程的使用。也可采用下图中作业上的题目。

SQL语句代码

--1.例1
use 学生作业管理数据库;
select * from 学生表;
select * from 课程表;
select * from 学生作业表;
--先查看是否存在名字为student_course的存储过程，如果有，删除
if exists (select name from  sysobjects where  name='student_course' and type='P')
drop procedure stuent_course;
--创建存储过程
create procedure student_course
as
	select 学生表.学号,姓名,课程名,作业1成绩
	from 学生表,课程表,学生作业表
	where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名='张艳'

--执行存储过程
execute student_course;

--带输入参数的存储过程
create procedure  student_course1
@Studentname varchar(10)
as
	select 学生表.学号,姓名,课程名,作业1成绩
	from 学生表,课程表,学生作业表
	where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名=@Studentname;

--调用student_course1
execute student_course1 '于兰兰';

--创建一个存储过程用于向学生表中插入记录
create procedure student_insert
@学号 int,@姓名 nvarchar(10),@性别 nvarchar(2),@专业班级 nvarchar(10),@出生日期 nvarchar(20),@联系电话 nvarchar(20)
as
	INSERT into 学生表
	values(@学号,@姓名,@性别,@专业班级,@出生日期,@联系电话);

--执行student_insert
execute student_insert '007','阿刚','男','电子06','2000-1-1','13333333333';


--创建存储过程，若没有给出学生姓名，则返回所有学生情况
create procedure student_course2
@StudentName nvarchar(20)=null
as
	if @StudentName is null
	begin 
		select 学生表.学号,姓名,课程名,作业1成绩
		from 学生表,课程表,学生作业表
		where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号
	end
	else
	begin
		select 学生表.学号,姓名,课程名,作业1成绩
		from 学生表,课程表,学生作业表
		where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名=@Studentname;
	end

execute student_course2 '张志国';
execute student_course2;			--使用默认参数值


--使用输出参数
CREATE procedure student_count
@CourseName varchar(20),
@StudentSum int output
as
	select @StudentSum=COUNT(*)
	from 课程表,学生作业表
	where 课程表.课程号=学生作业表.课程号 and 课程名=@CourseName;


--对于带有输出参数的存储过程，调用时需要定义相应的变量用于接收从存储过程返回的参数值
declare @StudentSum1 int;
execute student_count '数据结构',@StudentSum1 OUTPUT;
SELECT @StudentSum1 as 选数据结构的人数;

--创建一个存储过程，输出学生的基本情况
alter procedure student_query
@学号 int,@姓名 nvarchar(8) output,@性别 char(2) output
as
	select @姓名=姓名,@性别=性别
	from 学生表
	where 学号=@学号;

--调用存储过程，查看基本情况
declare @姓名1 nvarchar(20);
declare @性别1 char(2);
execute student_query '7',@姓名1 output,@性别1 output;
select @姓名1 as 学生姓名,@性别1 as 学生性别;
select * from 学生表;

--删除存储过程是
--drop procedure 存储过程名;

--market数据库中
use market;
--存储过程shanghai,查看上海客户信息
select * from Customers;
insert into Customers VALUES(3,'阿美','上海');
create procedure shanghai 
as
	select * from Customers
	where City='上海'

execute shanghai;
--存储过程Goods，查看指定商品信息，商品编号作为输入参数
select * from Goods;
insert into Goods values(1,'牙膏',2.5,'牙膏厂',400,'在售');
insert into Goods values(2,'牙刷',5,'牙刷厂',1200,'热卖');
create procedure cunchuGoods
@商品编号 int
as
	select * from Goods
	where GoodID=@商品编号;

execute cunchuGoods @商品编号=2;
--存储过程GoodsSum,查看指定客户的所有订单的订货总金额，客户编号作为输入参数，订货总金额作为输出参数
select * from Orders;
insert into Orders values(1,1,1,2,5,'2020-1-1');
insert into Orders values(2,1,2,2,10,'2020-1-1');
insert into Orders values(3,2,2,2,10,'2020-1-1');
create procedure cunchuGoodsSum
@客户编号 int,@订货总金额 float output
as 
	select @订货总金额=OrderSum from Orders
	where Orders.CustomerID=@客户编号;

declare @订货总金额 float;             --切记勿忘声明变量
execute cunchuGoodsSum   2,@订货总金额 output;
select @订货总金额 as '订货总金额';

--存储过程insert_Goods,向Goods表中插入一条记录
select * from Goods;
alter procedure insert_Goods
@商品编号 int,@商品名称 nvarchar(20),@价格 float,@供货商 nvarchar(20),@库存量 int,@商品状态 nvarchar(20)
as 
	insert into Goods(GoodID,Gname,Price,Provider,Stocks,Status) 
	values(@商品编号,@商品名称,@价格,@供货商,@库存量,@商品状态);

execute insert_Goods  3,'牙刷杯',10,'牙刷杯厂',5,'即将断货';
--创建存储过程Goods_Orders1，查看任何指定货品的订单情况，包括订单号，订货客户姓名以及订货数量（使用输入参数）
select * from Orders;
select * from Goods;
select * from Customers;
CREATE procedure Goods_Orders1
@指定货品 nvarchar(20)
as
	select OrderID,Cname,Quantity from Orders,Goods,Customers
	where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID AND  Goods.Gname=@指定货品;

EXECUTE Goods_Orders1 @指定货品='牙膏';


--执行存储过程，如果不给出参数则报错，如果希望不输入参数，即默认值，得到所有货品订单，则新建表Goods_Orders2
execute Goods_Orders1 @指定货品;  --报错
create procedure Goods_Orders2
@指定货品 nvarchar(20)=null
as
	if @指定货品 is null
	begin 
		select OrderID,Cname,Quantity from Orders,Goods,Customers
		where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID
	end
	else
	begin
	select OrderID,Cname,Quantity from Orders,Goods,Customers
	where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID AND  Goods.Gname=@指定货品
	end
execute Goods_Orders2;
execute Goods_Orders2 @指定货品='牙刷';

--创建存储过程Goods_OrderSum，来获得某个货品的订单总额（使用输入输出参数）
alter PROCEDURE Goods_OrderSum
@货品名称 nvarchar(20),@订单总额1 float output
as
	select @订单总额1=sum(OrderSum) from Orders,Goods
	where Goods.GoodID=Orders.GoodID and Gname=@货品名称;

declare @订单总额1 float;
execute Goods_OrderSum  '牙膏',@订单总额1 output;
select @订单总额1 as '订单总额';

实验10：触发器实验

自拟题目完成5个触发器的编写及调试，熟练掌握触发器的使用。也可采用下图中作业上的题目。

SQL语句代码

use 学生作业管理数据库;
--例8 创建一个触发器，当  学生表  中的记录被更新时，显示表中的所有记录
create trigger  student_change
	on 学生表 after insert,update,delete
	as
		select * from 学生表;

--查看下变化
select * from 学生表;
insert into 学生表 values(1,'阿美','女','计科06','2002-1-1','13312313213');

--例9 在  学生表  中创建DELETE触发器，实现对  学生表  和 学生作业表 的级联删除
create trigger studentdelete on 学生表
	after delete
as
	delete from 学生作业表
	where 学号 in
		(select deleted.学号 from deleted);
--查看下变化
select * from 学生作业表;
insert into  学生作业表 values('K001',1,99,99,99);
select * from 学生作业表;
delete  from 学生表 where 姓名='阿美';
select * from 学生作业表;

--例10 在学生作业表上创建insert 触发器，当向学生作业表 中添加学生的选课记录时，
--检查该学生的学号是否存在，若不存在，则不能将记录插入
create trigger sc_insert on 学生作业表
	after insert
as
	if(select count(*) from 学生表,inserted where 学生表.学号=inserted.学号)=0
	begin 
		print '学号不存在，不能插入'
		rollback transaction
	end;

--查看效果
insert into 学生作业表 values('K001',1,99,99,99);

--例11 创建update触发器，禁止对学生表 中学生的性别进行修改
create trigger student_update on 学生表
	after update
as 
	if update(性别)
	begin
		print '禁止对学生学号修改'
		rollback transaction
	end;
--查看效果
select * from 学生表;
update 学生表 set 性别='男' where 性别='女' and 学号=7;
select * from 学生表;

--例12 在学生作业表上创建触发器，当一次向学生作业表中添加多个记录时，删除学号在学生表中不存在的记录，
--从而保证数据的一致性，注意，不能在学生作业表中定义外键约束
create trigger sc_insert1 on 学生作业表 
	after insert
as 
	if(select count(*) from 学生表,inserted where inserted.学号=学生表.学号)<>@@ROWCOUNT
	BEGIN
		delete from 学生作业表
		where 学号 not in (select 学号 from 学生表)
	END;


--例13 在视图上定义instead of 触发器
select * from 学生表;
create view birth_view(学号,姓名,性别,生日,专业班级)
as 
	select 学号,姓名,性别,出生日期,专业班级
	from 学生表;

create trigger birth_view_insert on birth_view
instead of insert
as
	declare @学号 int
	declare @姓名 varchar(20);
	declare @性别 varchar(20);
	declare @生日 varchar(20);
	declare @专业班级 varchar(20);
	select 	@学号=学号,@姓名=姓名,@性别=性别,@专业班级=专业班级
	from inserted;
	insert into 学生表(学号,姓名,性别,专业班级) values(@学号,@姓名,@性别,@专业班级);
--查看效果
insert into birth_view(学号,姓名,性别,专业班级) values(2,'阿红','女','软件04');



use market;
--第四章第五题（5）在Customers表上建立删除触发器，实现Customers表和Orders表的级联删除
select * from Customers;
select * from Orders;

create trigger customers_delete on Customers
	after delete
as
	delete from Orders
	where CustomerID in (select deleted.CustomerID FROM DELETED);

--第四章第五题（6）在Orders表上建立插入触发器，当向表中添加一条订货记录时，若订单中的商品状态为即将断货（Status='即将断货'）
--则不能插入该条记录
select * from Customers;
select * from Orders;
select * from Goods;
CREATE trigger orders_insert on Orders
	AFTER INSERT
AS 
	if(select Status from Goods,inserted where Goods.GoodID=inserted.GoodID) in ('即将断货')
	begin
		print '即将断货，不能订购'
		rollback transaction
	end;
	
--试试效果
insert into Orders values(4,3,3,2,20,'2020-2-2');
--第四章第五题（7）在Orders表上建立插入触发器，当添加订单时，减少Goods表中相应商品的库存量
select * from Customers;
select * from Orders;
select * from Goods;

create trigger orders_insert1 on Orders
after insert
as 
	UPDATE Goods SET Stocks=Stocks-inserted.Quantity
	FROM Goods,inserted
	WHERE Goods.GoodID=inserted.GoodID;
	


--第四章第五题（8）在Orders表上建立触发器，不允许对订单日期进行修改
select * from Customers;
select * from Orders;
select * from Goods;
CREATE TRIGGER orders_create ON Orders
	AFTER UPDATE
as
	IF UPDATE(Date)
	BEGIN
	RAISERROR('不能手动修改',10,1)
	ROLLBACK TRANSACTION
	END;

--第四章第五题（9）建立触发器，实现参照完整性约束，即若在Orders表中添加一条记录时，则该订单中的商品也必须在
--Goods表中存在，否则不许添加该记录；
select * from Customers;
select * from Orders;
select * from Goods;

CREATE TRIGGER orders_create1 ON Orders
	AFTER INSERT
AS
	IF (SELECT COUNT(*) from Goods,inserted
		WHERE Goods.GoodId=inserted.GoodID)=0
	BEGIN
		print '这种货物不存在'
		rollback transaction
	END;

本文最初由security9968发布于security9968的csdn博客，禁止任何形式的剽窃行为
转载原创文章请注明，转载自：security的博客

年级		班号		组号		学号
专业		日期				姓名
实验名称	实验九  存储过程和触发器					实验室
实验 目的 或 要求	1、掌握存储过程的概念、作用、分类及对应的创建、删除语句的语法格式 2、掌握触发器的概念、作用、分类及对应的创建、删除语句的语法格式 3、了解插入表inserted和删除表deleted的作用及其用法
实验 环境	SQL Server 2014
实验内容或 实验题目	1、创建并执行一个无参数的存储过程proc_product1，通过该存储过程可以查询商品类别名称为“笔记本电脑”的商品的详细信息：包括商品编号、商品名称、品牌、库存量、单价和上架时间信息 2、创建并执行一个带输入参数的存储过程proc_product2，通过该存储过程可以根据输入参数供应商名称和包含关键字的商品名称进行商品具体信息的查询：包括商品编号、商品名称、品牌、库存量、单价、成本价、供应商名称、联系电话信息 3、创建并执行一个带输入参数和输出参数的存储过程proc_product3，通过该存储过程可以根据输入的会员用户名查询出该会员购买的商品信息：包括商品名称、购买数量和实付总金额信息 4、删除存储过程proc_product3 5、创建一个名为tri_insert1的DML触发器，该触发器的作用是：当向会员表member中添加一条记录时，如果新添加的会员编号已经存在于表中，则禁止插入该条记录，并提示该会员已经存在！。 6、创建一个名为tri_update1的DML触发器，该触发器的作用是：不允许修改订单表orders中的discount属性列的值，并给出提示语句“禁止修改orders表中的discount属性列的数据！” 7、创建一个名为tri_insert2的DML触发器，该触发器的作用是：当向employee新插入一条会员记录时，系统自动将部门表department中对应部门的属性列deptotal进行加1处理 8、创建一个名为tri_delete的DML触发器，该触发器的作用是：用删除会员表menmber中某条会员记录的时，系统自动在订单表orders中查询是否有该会员的订单记录，如果有，则同时删除该会员的所有订单记录 9、删除触发器tri_delete
实 验 原 理 （ 操 作 步 骤 ）	步骤如下： /*1、创建并执行一个无参数的存储过程proc_product1，通过该存储过程可以查询商品类别名称为 “笔记本电脑”的商品的详细信息：包括商品编号、商品名称、品牌、库存量、单价和上架时间信息*/ create procedure proc_product1 as select prono,proname,brand,stock,price,ontime from product where catno in( select catno from category where catname like'笔记本电脑' ) go exec proc_product1 go /*2、创建并执行一个带输入参数的存储过程proc_product2，通过该存储过程可以根据输入参数供应商名称 和包含关键字的商品名称进行商品具体信息的查询：包括商品编号、商品名称、品牌、库存量、单价、成本价、供应商名称、联系电话信息*/ create procedure proc_product2(@name1 varchar(30),@name2 varchar(30)) as select product.prono,proname,brand,stock,price,cost,supplier.supname,telephone from product,supplier where  product.supno=supplier.supno and  supplier.supname=@name1 and product.proname=@name2 exec proc_product2 '顺京通讯有限公司','华为P50 4G全网通智能手机' go /*3、创建并执行一个带输入参数和输出参数的存储过程proc_product3，通过该存储过程可以根据输入的会员 用户名查询出该会员购买的商品信息：包括商品名称、购买数量和实付总金额信息*/ create procedure proc_product3(@name3 varchar(30)) as select product.proname,orders.qty,tatalmoney from product,orders,member where product.prono=orders.prono and member.memno=orders.memno and memname=@name3 go exec proc_product3 '关羽' go /*4、删除存储过程proc_product3*/ drop procedure proc_product3 go /*5、创建一个名为tri_insert1的DML触发器，该触发器的作用是：当向会员表member中添加一条记录时，如 果新添加的会员编号已经存在于表中，则禁止插入该条记录，并提示该会员已经存在！。*/ create trigger tri_insert1 on member instead of insert as declare @no int select @no =memno from inserted if exists(select memno from member where memno=@no) begin rollback transaction print'会员已存在！' end else begin  insert into member select*from inserted  print'完成' end go insert into member values('2001','李四','上海','10086','cd','123456') go /*6、创建一个名为tri_update1的DML触发器，该触发器的作用是：不允许修改订单表orders中的discount属 性列的值，并给出提示语句“禁止修改orders表中的discount属性列的数据！”*/ create trigger tri_update1 on orders after update as if update(discount) begin rollback transaction print'禁止修改orders表中的discount属性列的数据！' end go update orders set discount =110 where orderno='1501001' go /*7、创建一个名为tri_insert2的DML触发器，该触发器的作用是：当向employee新插入一条会员记录时，系 统自动将部门表department中对应部门的属性列deptotal进行加1处理*/ go create trigger tri_insert2 on employee after insert as begin  update department  set deptotal = deptotal+1  where depno = (select depno from inserted); end go insert into employee values('1007','小张','1','男','1111111','xxx','123456') select* from department /*8、创建一个名为tri_delete的DML触发器，该触发器的作用是：用删除会员表menmber中某条会员记录的时， 系统自动在订单表orders中查询是否有该会员的订单记录，如果有，则同时删除该会员的所有订单记录*/ go create trigger tri_delete on member for delete as delete from orders where memno=(select memno from deleted) delete from member where memname='关平' select *from orders /*9、删除触发器tri_delete*/ drop trigger tri_delete （写不完时，可另加附页。）

组内 分工 （ 可选 ）

实 验 结 果 分 析 及 心 得 体 会

实验运行结果或者是验证性的结果: 1. /*1、创建并执行一个无参数的存储过程proc_product1，通过该存储过程可以查询商品类别名称为 “笔记本电脑”的商品的详细信息：包括商品编号、商品名称、品牌、库存量、单价和上架时间信息*/ create procedure proc_product1 as select prono,proname,brand,stock,price,ontime from product where catno in( select catno from category where catname like'笔记本电脑' ) go exec proc_product1 go   2. /*2、创建并执行一个带输入参数的存储过程proc_product2，通过该存储过程可以根据输入参数供应商名称 和包含关键字的商品名称进行商品具体信息的查询：包括商品编号、商品名称、品牌、库存量、单价、成本价、供应商名称、联系电话信息*/ create procedure proc_product2(@name1 varchar(30),@name2 varchar(30)) as select product.prono,proname,brand,stock,price,cost,supplier.supname,telephone from product,supplier where  product.supno=supplier.supno and  supplier.supname=@name1 and product.proname=@name2 exec proc_product2 '顺京通讯有限公司','华为P50 4G全网通智能手机' go  3. /*3、创建并执行一个带输入参数和输出参数的存储过程proc_product3，通过该存储过程可以根据输入的会员 用户名查询出该会员购买的商品信息：包括商品名称、购买数量和实付总金额信息*/ create procedure proc_product3(@name3 varchar(30)) as select product.proname,orders.qty,tatalmoney from product,orders,member where product.prono=orders.prono and member.memno=orders.memno and memname=@name3 go exec proc_product3 '关羽' go /*4、删除存储过程proc_product3*/ drop procedure proc_product3 go /*5、创建一个名为tri_insert1的DML触发器，该触发器的作用是：当向会员表member中添加一条记录时，如 果新添加的会员编号已经存在于表中，则禁止插入该条记录，并提示该会员已经存在！。*/ create trigger tri_insert1 on member instead of insert as declare @no int select @no =memno from inserted if exists(select memno from member where memno=@no) begin rollback transaction print'会员已存在！' end else begin  insert into member select*from inserted  print'完成' end go insert into member values('2001','李四','上海','10086','cd','123456') go   5.  6. *6、创建一个名为tri_update1的DML触发器，该触发器的作用是：不允许修改订单表orders中的discount属 性列的值，并给出提示语句“禁止修改orders表中的discount属性列的数据！”*/ create trigger tri_update1 on orders after update as if update(discount) begin rollback transaction print'禁止修改orders表中的discount属性列的数据！' end go update orders set discount =110 where orderno='1501001' go  7. /*7、创建一个名为tri_insert2的DML触发器，该触发器的作用是：当向employee新插入一条会员记录时，系 统自动将部门表department中对应部门的属性列deptotal进行加1处理*/ go create trigger tri_insert2 on employee after insert as begin  update department  set deptotal = deptotal+1  where depno = (select depno from inserted); end go insert into employee values('1007','小张','1','男','1111111','xxx','123456') select* from department 前： 后： 8. *8、创建一个名为tri_delete的DML触发器，该触发器的作用是：用删除会员表menmber中某条会员记录的时， 系统自动在订单表orders中查询是否有该会员的订单记录，如果有，则同时删除该会员的所有订单记录*/ go create trigger tri_delete on member for delete as delete from orders where memno=(select memno from deleted) delete from member where memname='关平' select *from orders  关平为 2009    /*9、删除触发器tri_delete*/ drop trigger tri_delete 分析结果以及在试验中应注意的问题: 写错no和name 所以数据类型错了

实验名称：存储过程和触发器

实验目的：

1.掌握存储过程的概念、功能等

2.掌握用户自定义存储过程的创建、修改、删除和执行

3.掌握触发器的概念和功能

4.掌握触发器的创建、修改、删除及其使用方法

实验意义：

1.存储过程的创建可以对数据操作过程加密，使用户只能输入相应参数完成操作得到输出结果，并不能查看到源代码，对于数据库的安全性具有重要意义。

2.存储过程效率较高，通过自定义的存储过程，可以反复进行调用，避免多次重复输入相似的代码从而降低效率。

3.触发器的存在可以使表与表之间实现一定的约束或者实现级联更新的操作，通过触发器的创立，用户在执行某些操作时可能会不满足条件而终止，也可以产生相应的该表或者其他表的数据更新的效果。

实验内容：

• 创建并执行存储过程
• 修改存储过程
• 删除存储过程
• 创建和执行触发器
• 修改触发器
• 删除触发器
• 习题
• 总结与心得体会

实验主要步骤：

• 创建并执行存储过程

1.交互式创建存储过程

在打开显示的界面中，修改参数，输入SQL语句进行创建存储过程如下，通过学生学号来查询学生的姓名、年龄、系名，并设置默认学号为S2。在存储过程中声明变量，定义参数，执行查询SQL赋值语句，并且将查询结果传出存储过程：

       执行SQL语句，调用创建的存储过程，先调用参数为默认学号为S2的学生查询存储过程，再调用学号参数为S4的学生的查询过程，并且打印出学号，年龄与系别。

2.T-SQL语句创建存储过程

       通过学生姓名和课程名查询该生该课程的成绩

新建查询，创建存储过程，首先声明变量，并且声明变量的类型，采用表的连接方式，并且执行条件语句WHERE选中该学生与该课程，在SELECT中即可完成参数的传出。

       执行T-SQL语句，同样先声明变量与存储类型，再采用SELECT进行赋值操作，也可以采用SET对学生名与课程名进行赋值，最后再调用存储过程，将参数输入，最终在调用存储过程后将学生姓名与选课成绩打印出来。

• 修改存储过程

1.交互式修改存储过程Pro_Qsinf

在对象资源管理器中，找到对应表下的存储过程，右击该存储过程，点击“修改”，将变量SNO_IN的长度修改为2，输出变量SAGE_OUT的类型改为TINYINT。

修改之后的结果如下所示，并单击执行，将其再次保存。

2.T-SQL语句式修改存储过程Pro_Qsinf

       执行方法是采用Alter语句，将存储过程整体结构重新编写修改，修改查询结果为查询姓名、性别与系名，并且将学号的默认值修改S1

最后在对象资源管理器中将该存储过程刷新后可以看到，该存储过程的列参数的参数类型已经发生对应的变化。

• 删除存储过程

1.交互式删除存储过程Pro_Qsinf

在存储过程中，右击该存储过程，单击删除操作，点击确定即可完成对存储过程的删除操作。

2.T-SQL语句对存储过程进行删除

• 创建和执行触发器

1.交互式为数据库表S创建级联更新的触发器TRIGGER_S

       该触发器作用：修改表S中学号时，SC表中该学生选课记录的学号相应发生变化，与外键相似的效果。

       在对象资源管理器中对于表C新建触发器如下：

       采用与之前新建存储过程相似的方法，将交互式中自带的语句进行修改如下，先提前定义运行条件是当表的SNO发生更新时才执行，再声明变量，进行查询语句创建操作，并且设置对应学号发生变化后，SC表中对应的学号数据也要发生变化，采用SET语句对SC表相应学号进行赋值。

       数据校验，将学号为S1学生修改学号为S5：

       执行之后，查看学生表的数据，学生“赵就”的学号由S1变成了S5

       再查看SC表中的数据，发现原本为S1学生的选课数据变成了S5的选课数据，说明触发器的创建成功。

2.交互式为数据库表SC创建限制更新触发器TRIGGER_SC

修改SC表中一记录的学号，则要求检查表S中是否存在学号相同的记录，若存在则不许修改。

以下为代码创建过程：

对SC表创建触发器，由于更新过程涉及到表DELETED，因此将删除的SNO保存为SNO_OLD，并对S表中的SNO与更新SC表前的SNO进行对比，若存在1或多条数据，由COUNT函数记录，并且当COUNT函数结果不等于0时，触发器停止运行，过程终止(ROLLBACK TRANSACTION)。

对SC表更新学号，观察S表中学号变化情况，可以看出触发器可以正常运转，并且将批处理中止。

3.用SQL语句为数据库表SC创建成绩约束触发器

       对更新后插入表内成绩进行判断，满足范围才能执行改变，范围不满足即会中止。

在SC表中插入不合格成绩数据进行测试

插入成绩合格数据再进行测试

修改更新表中数据，不合格数据如下：

修改更新表中数据，合格数据如下：

综合以上四种测试情况，可以验证出该触发器的创建的正确性。

4.用SQL语句为数据库表C创建个级联删除触发器TRIGGER_DC

       创建触发器，并且设定触发器条件为DELETE，接下来设置具体触发条件，目标为从C表的改变到SC表中课程的删除。

测试数据，对C表删除课程C1如下：

       可以查看SC表中课程C1已经不存在：

• 修改触发器

1.交互式修改数据库表S的触发器TRIGGER_S

       与修改存储过程方法极其类似，在对象资源管理器中右击触发器，单击修改。

       修改为删除表S中信息，自动删除数据库表中与该学生相关的记录。

2.用SQL语句修改数据库表C的触发器TRIGGER_DC

       在表C中删除某课程信息，同时删除数据库表SC与TC的相关课程记录。在触发器中，增加DELETE语句，作用于SC与TC表，并且设置删除条件

• 删除触发器

1.交互式删除触发器

       操作步骤如下图所示：

2.T-SQL语句删除触发器

习题训练

存储过程习题

1.利用学生姓名查询该生选修的课程名、成绩，以及任课教师名

       首先对存储过程进行创建，声明变量，定义参数，在查询语句中，采用SELECT输出课程名、成绩，以及任课教师名，采用多表连接的方式，并且进行设定筛选条件，打印出结果。

       调用该过程程序语句如下，经过测试仅能输出一行程序运行结果，并不满足我们真实的需求，因此在未增加游标的情况下，该存储过程执行结果是并不完整的。

3.利用学生姓名和课程名检索该生该课程的成绩

       仍然采用多表连接的方式，输出结果为年龄，查询过程比较简单，创建存储过程如下：

调用存储过程的结果如下，选用一组测试数据，并将结果打印在了显示页面上如下：

7.利用课程名查询选修该课程的学生姓名、系别、成绩，并给出某门课程的查询信息。

       在创建存储过程中，采用多表连接的方式，并确定输出列，同时打印出结果。

       调用存储过程如下，选择课程为“定位原理”，可以看出基于美存在游标的情况，输出结果仍然只是单行数据，是基于SELECT的赋值方式，仅赋值最后一次赋值的结果。

8.利用教师姓名和课程名检索该教师该认可的课程名、课时数、选课人数、平均成绩、最高成绩。

       创建存储过程如下，查询语句中，记录输出的课程名、课时数、并且记录数目、平均成绩值、最高成绩值，同时采用多表连接的方法，判断筛选条件为教师姓名与课程名，并且按照教师姓名、课程名以及课时数进行分组执行查询操作。

       调用存储过程如下，并且给出测试用例，输出结果打印至显示页面上：

触发器习题

1.为数据库表SC创建触发器：当插入或修改一个记录时，确保此纪录成绩在0-100分之间。

采用判断语句，对插入或更新表中成绩数据时，进行判断成绩的范围，满足时打印插入或修改成功，不满足时触发器自动中止该过程。

接下来是四种情况验证触发器的创建效果：

可以看出创建成功，可以符合逻辑正常运转。

3.为数据库表C、TC和SC创建参照完整性：级联删除和级联修改触发器

       主要过程实现为对C表创建触发器，触发器实现条件为C表的更新与删除，并且是对于SC与TC表相对于课程发生变化，触发器创建如下：

测试更新表CNO数据如下：

       可以看出SC与TC均发生相应的变化，课程C3已经全部转化为了C8如下：

 

       再次测试删除操作如下：

       可以看出SC与TC均发生相应的变化，课程C7已经全部被删除如下：

 

       综上所述，可以看出修改和删除操作均顺利执行！

4.为数据库表T创建触发器：当职称从“讲师”晋升为“副教授”时，岗位津贴自动增加500元，从“副教授”晋升为“教授”时，津贴自动增加900元。

       创建存储过程，该过程是单表内部的级联更新，先在初始表中保存工资值，再删除表中保存删除的职称，在插入表中保存插入的职称，接下来对职称进行判断，满足“讲师”到“副教授”，则设置其工资在原始工资上增加500元，若满足“副教授”到“教授”，再初始工资上增加900元。代码如下：

展示初始的教师T表如下：

执行从“讲师”到“副教授”过程，并且查看结果：

结果如下，可以看出正常运转：

执行从“副教授”到“教授”过程，并且查看结果：

结果如下，可以看出正常运转：

总结与实验心得

（一）实验代码优化部分：

题目：修改SC表中一记录的学号，则要求检查表S中是否存在学号相同的记录，若存在则不许修改。

采用EXISTS语句进行比较效率更加高，因此将前边创建触发器中更新触发器中连接过程修改为之间判断是否存在的过程，提高了SQL代码的执行效率，在进行数据庞大的表时，效率更高，代码对比如下：

优化前：

优化后：

（二）总结

1.在声明变量时需要先确定变量的类型与确定其是为输入或输出变量，对变量进行命名时对变量进行标记输入或输出，并且尽量取名与字面意思相同的名称，便于程序的编写。

2.在定义存储过程时需要注意是否需要给查询输入的参数赋值默认值，有默认值的情况更利于存储过程的调用。

3. 对于调用存储过程时虽然不能使存储过程返回多条数据，但是通过运行调试发现，可以在存储过程中按照常规SQL语句执行，显示出多条数据在结果栏中。

修改存储过程如下：

调用存储过程并且返回查询结果如下：

       此过程并未进行赋值，也没有通过存储过程将查询结果数据传出，仅仅是在存储过程中通过查询语句得到查询表结果的页面显示上。具体的多行数据传出过程需要通过后续学习的游标才能真正完成实现。

实验体会存储过程与之前所学编程中函数的异同点：

不同方面：函数一般是之间包含在主程序中的，在同一界面下直接进行调用，而存储过程是单独作为打包文件存在数据库下。并且存储过程是返回对应参数，而函数只是返回值，同时存储过程功能比较强大，可以在其中直接进行表临时操作等。

相同方面：本质相同，均是对语句的打包执行，提高程序代码编写的效率且具有更强的安全性。

存储过程与触发器的差异：

存储过程主要是对于查询并且返回结果的语句执行的打包操作，需要再次执行调用才能完成某种查询执行操作，并且主要是针对查询操作返回参数结果。

触发器主要是实现级联更新、插入、删除等操作，是提前定义好的并且是针对特定的表，可以是针对单表内数据发生变化该表内其他数据对应属性值的变化，或者是单表数据发生变化后引起其他表数据相应的发生变化的过程，是一种数据发生变化后不需要调用而自动执行的过程。

触发器相对于外键的优势：功能比外键更加强大，外键仅仅是通过表的外连接，限制多表数据删除或者插入时的功能；而触发器除了包含外键的功能之外，同时还包含了域约束的效果，以及可以针对单表内进行数据级联更新变化，效果更加全面，而且多表之间也可以提前建立触发器关系，相应数据变化产生的其他数据对应的变化。