| 编号 | 人员 | 题目 | 总分数 | 题干 | 提交内容 | 得分标准 |
| 7 | 程序员 | 触发器 | 15 | trigger要求： 1、用户表添加语句添加触发器，要求在添加用户信息时同时初始化用户钱包表数据，初始金额为0。 2、商品表修改语句添加触发器，要求在修改商品售价时不允许上下浮动超过10%。 3、订单表删除语句添加触发器，要求在删除订单信息时先删除订单详情表中的订单信息。 |
提交3条sql与对应的结果截图 | 1、要求给出触发器完整函数语句(3分/问，共9分) 2、触发器中有明确的注释(3分) 3、给出触发器测试语句(3分) |
| 8 | 程序员 | 存储过程 | 20 | percedure基本要求： 1、添加一个用户下订单的存储过程，存储过程名称叫做【create_order_infos()】 2、要求传入创建订单所必须的参数内容，例如：用户编号、商品编号、购买数量等信息。 3、需要根据传入的信息插入【用户钱包交易日志表】、【订单表】、【订单详情表】信息，修改【用户钱包表】、【商品表】。 |
提交完整的存储过程语句与测试语句。 | 1、函数名称匹配(1分) 2、正确传入参数(3分) 3、写明存储过程的详细注释(5分) 4、正确添加所有日志信息(6分) 5、正确添加订单与订单详情(6分) 6、正确修改用户钱包表与商品表数值(6分) 7、给出存储过程测试语句(3分) |
触发器
1、用户表添加语句添加触发器，要求在添加用户信息时同时初始化用户钱包表数据，初始金额为0。
-- 触发器：在user表插入新记录后，自动为新用户创建wallet记录 DELIMITER // CREATE TRIGGER after_user_insert AFTER INSERT ON user FOR EACH ROW BEGIN -- 向user_wallet表中插入新记录，user_id使用新插入的用户的ID，balance初始化为0.00 INSERT INTO user_wallet (user_id, balance) VALUES (NEW.user_id, 0.00); END; // DELIMITER ;
测试语句：
-- 插入新用户记录，触发器应自动在user_wallet表中创建对应wallet记录 INSERT INTO user (username, password, email, phone) VALUES ('newuser', 'password123', 'newuser@example.com', '1234567890'); -- 查询user_wallet表确认新wallet记录是否存在 SELECT * FROM user_wallet WHERE user_id = (SELECT user_id FROM user WHERE username = 'newuser');
2、商品表修改语句添加触发器，要求在修改商品售价时不允许上下浮动超过10%。
DELIMITER // CREATE TRIGGER trg_check_price_change BEFORE UPDATE ON product FOR EACH ROW BEGIN -- 声明变量来存储旧价格和新价格 DECLARE old_price DECIMAL(10, 2); DECLARE new_price DECIMAL(10, 2); -- 声明变量来存储价格变动的百分比 DECLARE percentage_change DECIMAL(10, 2); -- 获取旧价格和新价格 SET old_price = OLD.price; SET new_price = NEW.price; -- 检查新价格是否为0，以避免除以0的错误 IF new_price = 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '新价格不能为0。'; END IF; -- 计算价格变动的百分比 SET percentage_change = (new_price - old_price) / old_price * 100; -- 检查价格变动是否超过10% IF ABS(percentage_change) > 10 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '商品售价上下浮动不能超过10%。'; END IF; END; // DELIMITER ;
测试语句：
--插入测试数据： INSERT INTO product (product_name, price, stock, type_id) VALUES ('Test Product', 100.00, 10, 1); --正常更新（未超过10%）： UPDATE product SET price = 110.00 WHERE product_name = 'Test Product'; -- 应成功 更新超过10%（触发错误）： UPDATE product SET price = 550.00 WHERE product_name = 'Test Product'; -- 应触发错误
手动修改直接会触发。
3、订单表删除语句添加触发器，要求在删除订单信息时先删除订单详情表中的订单信息。
DELIMITER // CREATE TRIGGER trg_delete_order_details BEFORE DELETE ON `order` FOR EACH ROW BEGIN -- 删除与即将被删除的订单相关联的所有订单详情记录 DELETE FROM order_info WHERE order_id = OLD.order_id; END; // DELIMITER ;
测试语句：
插入测试数据到order和order_info表，用做测试。
INSERT INTO `order` (`user_id`, `order_status`, `total_price`) VALUES (1, '待支付', 100.00); INSERT INTO order_info (order_id, product_id, quantity, unit_price) VALUES (LAST_INSERT_ID(), 1, 2, 50.00);
执行删除操作，并观察order_info表的变化。
DELETE FROM `order` WHERE `order_id` = 刚才添加的id号;
检查order_info表，确保与刚才删除的订单相关的记录已被删除。
SELECT * FROM order_info WHERE order_id = 刚才添加的id号;
