【数据库原理】实验报告-实验9 存储过程和触发器

实验 目的 或 要求	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触发器，该触发器的作用是：当向会员表member新插入一条会员记录时，系统自动将部门表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   2、创建并执行一个带输入参数的存储过程proc_product2，通过该存储过程可以根据输入参数供应商名称和包含关键字的商品名称进行商品具体信息的查询：包括商品编号、商品名称、品牌、库存量、单价、成本价、供应商名称、联系电话信息 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 + '%'   3、创建并执行一个带输入参数和输出参数的存储过程proc_product3，通过该存储过程可以根据输入的会员用户名查询出该会员购买的商品信息：包括商品名称、购买数量和实付总金额信息 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   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; 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 member 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 drop trigger tri_delete

组内 分工 （ 可选 ）

实 验 结 果 分 析 及 心 得 体 会

实验运行结果或者是验证性的结果: 1.成功创建并执行了无参数存储过程 proc_product1，查询出了商品类别名称为“笔记本电脑”的商品的详细信息。 2.成功创建并执行了带输入参数的存储过程 proc_product2，根据输入的供应商名称和包含关键字的商品名称查询出了商品的详细信息。 3.成功创建并执行了带输入参数和输出参数的存储过程 proc_product3，根据输入的会员用户名查询出了该会员购买的商品信息。 4.成功删除了存储过程 proc_product3。 5.成功创建了DML触发器 tri_insert1，当向会员表 member 中添加已存在的会员编号时，阻止了插入并提示会员已经存在。 6.成功创建了DML触发器 tri_update1，在尝试修改订单表 orders 中的 discount 属性列的值时，阻止了修改并提示“禁止修改 orders 表中的 discount 属性列的数据！” 7.成功创建了DML触发器 tri_insert2，在向会员表 member 新插入一条会员记录时，自动将部门表 department 中对应部门的属性列 deptotal 加1。 8.成功创建了DML触发器 tri_delete，在删除会员表 member 中某条会员记录时，自动删除了订单表 orders 中该会员的所有订单记录。 9.成功删除了触发器 tri_delete。 分析结果以及在试验中应注意的问题: 1.数据完整性: 在执行插入、更新、删除操作时，需确保不会破坏数据库的参照完整性。如删除记录前检查是否有其他表引用该记录。每完成一步操作后，应进行验证，比如通过查询确认数据是否正确插入、更新或删除，确保结果符合预期。 2. 视图验证: 创建视图时，需要确保视图的定义符合预期的数据选择条件，并在实际查询中验证视图的正确性。 3. 触发器: 在创建触发器时，确保触发器逻辑不会引发死锁或循环触发。例如，插入触发器不应再次触发自身，避免递归调用。触发器的操作应尽量简洁高效，避免复杂的业务逻辑。