MySQL周内训参照4、触发器-插入-修改-删除

编号人员题目总分数题干提交内容得分标准7程序员触发器15trigger要求：
1、用户表添加语句添加触发器，要求在添加用户信息时同时初始化用户钱包表数据，初始金额为0。
2、商品表修改语句添加触发器，要求在修改商品售价时不允许上下浮动超过10%。
3、订单表删除语句添加触发器，要求在删除订单信息时先删除订单详情表中的订单信息。提交3条sql与对应的结果截图1、要求给出触发器完整函数语句(3分/问，共9分)
2、触发器中有明确的注释(3分)
3、给出触发器测试语句(3分)8程序员存储过程20percedure基本要求：
1、添加一个用户下订单的存储过程，存储过程名称叫做【create_order_infos()】
2、要求传入创建订单所必须的参数内容，例如：用户编号、商品编号、购买数量等信息。
3、需要根据传入的信息插入【用户钱包交易日志表】、【订单表】、【订单详情表】信息，修改【用户钱包表】、【商品表】。提交完整的存储过程语句与测试语句。1、函数名称匹配(1分)
2、正确传入参数(3分)
3、写明存储过程的详细注释(5分)
4、正确添加所有日志信息(6分)
5、正确添加订单与订单详情(6分)
6、正确修改用户钱包表与商品表数值(6分)
7、给出存储过程测试语句(3分)

触发器

1、用户表添加语句添加触发器，要求在添加用户信息时同时初始化用户钱包表数据，初始金额为0。

-- 触发器名称：after_user_insert

-- 功能描述：在user表插入新记录后，自动为新用户创建wallet记录

-- 触发时机：AFTER INSERT

-- 触发对象：user表

-- 触发行为：FOR EACH ROW（对每一行插入操作触发）
DELIMITER $$  -- 更改默认的语句分隔符为$$，这样可以在触发器内部使用分号
CREATE TRIGGER after_user_insert  -- 创建一个名为after_user_insert的触发器
AFTER INSERT ON user  -- 触发器在user表发生INSERT操作之后触发
FOR EACH ROW  -- 触发器对每一行插入操作都执行一次
BEGIN  -- 触发器开始
    INSERT INTO user_wallet (user_id, balance)  -- 在user_wallet表中插入一行数据
    VALUES (NEW.user_id, 0.00);  -- 使用NEW.user_id作为新插入行的user_id，balance为0.00
END;  -- 触发器结束
$$  -- 触发器定义结束，使用新的分隔符
DELIMITER ;  -- 将语句分隔符改回为分号

测试语句：

-- 插入新用户记录，触发器应自动在user_wallet表中创建对应wallet记录
INSERT INTO user (username, password, email, phone)
VALUES ('孙燕姿', 'password123', '[email protected]', '1234567890');

-- 查询user_wallet表确认新wallet记录是否存在
-- 完整的多表联合查询确认
select u.*,uw.balance from `user` u INNER JOIN user_wallet uw on u.user_id=uw.user_id where username='孙燕姿'; 
-- 简单的子查询
SELECT * FROM user_wallet WHERE user_id = (SELECT user_id FROM user WHERE username = '孙燕姿');

2、商品表修改语句添加触发器，要求在修改商品售价时不允许上下浮动超过10%。

基本示例

begin 
    -- 编写代码的区域
    DECLARE result1 decimal(10,4); -- 声明变量-必须用在头部
    if new.price=0 then
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '新价格不能为0。';
    end if;
    -- 题目要求加上上下浮动不得超过10%
    set result1 = (new.price-old.price)/old.price*100;
    if abs(result1)>10 then
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '价格上下浮动不得超过10%。';
    end if;
end

详细示例

-- 使用两个美元符号（$$）作为语句定界符，这允许在触发器内部使用分号（;）
DELIMITER $$

-- 创建触发器，命名为trg_check_price_change

-- 在product表的每一行数据更新之前执行此触发器
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);
    
    -- 将旧的价格值赋给old_price变量，这里的OLD是MySQL提供的关键字，用于获取更新前的字段值
    SET old_price = OLD.price;
    -- 将新的价格值赋给new_price变量，NEW关键字用于获取更新后的字段值
    SET new_price = NEW.price;
    
    -- 接下来检查新的价格是否为0，因为商品的价格通常不应该为0
    -- 如果是，则触发一个错误信号，并返回指定的错误消息
    IF new_price = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '新价格不能为0。';
    END IF;
    
    -- 计算价格变动的百分比
    -- 使用公式：(新价格 - 旧价格) / 旧价格 * 100
    -- 这里需要确保old_price不为0，否则除以0会导致错误
    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 ('大鱼头', 100.00, 10, 1);

-- 正常更新（未超过10%）：
UPDATE product SET price = 110.00 WHERE product_name = '大鱼头'; -- 应成功

-- 更新超过10%（触发错误）：
UPDATE product SET price = 550.00 WHERE product_name = '大鱼头'; -- 应触发错误

手动修改直接会触发。

3、订单表删除语句添加触发器，要求在删除订单信息时先删除订单详情表中的订单信息。

这个题目考查的是前后的问题，必须删除从表数据后才能删除主表数据，故而这里是【BEFORE】

DELIMITER $$  -- 更改语句分隔符为$$，这样可以在触发器内部使用分号
CREATE TRIGGER trg_delete_order_details
BEFORE DELETE ON `order`  -- 在`order`表执行DELETE操作之前触发order是关键字需要加上``区分关键字
FOR EACH ROW  -- 对每行被删除的数据执行一次触发器
BEGIN
    -- 删除与即将被删除的订单相关联的所有订单详情记录
    DELETE FROM order_info WHERE order_id = OLD.order_id;  -- OLD是MySQL中代表被删除行的关键字
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号;