数据库 SQL 实验报告存储过程和触发器

年级		班号		组号		学号
专业		日期				姓名
实验名称	实验九  存储过程和触发器					实验室
实验 目的 或 要求	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 所以数据类型错了