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

触发器

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', 'newuser@example.com', '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 ;

详细示例

 
-- 使用两个美元符号（$$）作为语句定界符，这允许在触发器内部使用分号（;）
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 = '大鱼头'; -- 应触发错误