《数据库原理》实验九 存储过程和触发器

本系列传送门：

1. 实验二 SQL Server SSMS工具创建和管理数据库及数据表
2. 实验三 SQL Server SSMS工具添加数据
3. 实验四 SQL的数据定义语句
4. 实验五 数据库完整性约束的实现与验证
5. 实验六 SQL数据查询实验
6. 实验六（2） SQL数据查询—连接查询
7. 实验七 SQL的数据更新和视图
8. 实验八 T_SQL编程
9. 实验九 存储过程和触发器
10. 实验十 索引和数据库的安全性

1. 本实验的数据来源

可参考 实验三 SQL Server SSMS工具添加数据的导入脚本。

2. 实验目的

1、掌握存储过程的概念、作用、分类及对应的创建、删除语句的语法格式
2、掌握触发器的概念、作用、分类及对应的创建、删除语句的语法格式
3、了解插入表inserted和删除表deleted的作用及其用法

3. 实验内容

1、创建并执行一个无参数的存储过程proc_product1，通过该存储过程可以查询商品类别名称为“笔记本电脑”的商品的详细信息：包括商品编号、商品名称、品牌、库存量、单价和上架时间信息

go
Create procedure proc_product1 as
select prono, proname, brand, stock, price, ontime from product
go

2、创建并执行一个带输入参数的存储过程proc_product2，通过该存储过程可以根据输入参数供应商名称和包含关键字的商品名称进行商品具体信息的查询：包括商品编号、商品名称、品牌、库存量、单价、成本价、供应商名称、联系电话信息

go
Create  procedure  proc_product2(@supname varchar(255), @keyword varchar(255))
as
select prono, proname, brand,
 stock, price, cost, supname, telephone
from product
left join supplier
on supplier.supno = product.supno
where supname = @supname and proname like '%' + @keyword + '%'
go

EXECUTE proc_product2 '顺京通讯有限公司', '华为' 

3、创建并执行一个带输入参数和输出参数的存储过程proc_product3，通过该存储过程可以根据输入的会员用户名查询出该会员购买的商品信息：包括商品名称、购买数量和实付总金额信息

感谢 @m0_69646415 勘误

go
Create procedure proc_product3
@memname varchar(50)
as
select proname,qty,totalmoney
from member,product,orders
where member.memno=orders.memno and orders.prono=product.prono and memname = @memname
go
exec proc_product3 '孙晓红'
/*查询的时候换个人，别查孙晓红了*/

4、删除存储过程proc_product3

drop procedure proc_product3

5、创建一个名为tri_insert1的DML触发器，该触发器的作用是：当向会员表member中添加一条记录时，如果新添加的会员编号已经存在于表中，则禁止插入该条记录，并提示该会员已经存在！。

CREATE TRIGGER tri_insert1
on member
for insert
as
begin
declare @memno int;
select @memno = memno from inserted;
select * from member where memno = @memno;
if exists(select * from member where memno = @memno)
	begin
	rollback tran
	print '不允许添加'
	end
end

6、创建一个名为tri_update1的DML触发器，该触发器的作用是：不允许修改订单表orders中的discount属性列的值，并给出提示语句“禁止修改orders表中的discount属性列的数据！”

CREATE TRIGGER tri_update1
on orders
after update
as
if update(discount)
	begin
	rollback tran
	print'禁止修改orders表中的discount属性列的数据！'
	end

7、创建一个名为tri_insert2的DML触发器，该触发器的作用是：当向会员表member新插入一条会员记录时，系统自动将部门表department中对应部门的属性列deptotal进行加1处理

CREATE TRIGGER tri_insert2
on employee
after insert
as
begin
	update department
	set deptotal = deptotal+1
	where depno = (select depno from inserted);
end

8、创建一个名为tri_delete的DML触发器，该触发器的作用是：用删除会员表menmber中某条会员记录的时，系统自动在订单表orders中查询是否有该会员的订单记录，如果有，则同时删除该会员的所有订单记录

CREATE TRIGGER tri_delete
on member
for delete
as
delete from orders
where memno=(select memno from deleted)

9、删除触发器tri_delete