触发器——插入——修改——删除

1、用户表添加语句添加触发器

-- 触发器
DELIMITER $$  
CREATE TRIGGER after_user_insert  
AFTER INSERT ON user 
FOR EACH ROW  
BEGIN  
    INSERT INTO user_wallet (user_id, balance)  
    VALUES (NEW.user_id, 0.00);  
END; 
$$  
DELIMITER ;  

INSERT INTO user (username, password, email, phone)
VALUES ('孙燕姿', 'password123', 'newuser@example.com', '1234567890');
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、商品表修改语句添加触发器

-- 商品表修改语句添加触发器
 
-- 使用两个美元符号（$$）作为语句定界符，这允许在触发器内部使用分号（;）
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;
    IF new_price = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '新价格不能为0。';
    END IF;
    SET percentage_change = (new_price - old_price) / old_price * 100;
    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 = 120.00 WHERE product_name = '麦当当'; 
 
-- （触发错误）：
UPDATE product SET price = 880.00 WHERE product_name = '麦当当'; 

 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 ;  

INSERT INTO `order` (`user_id`, `order_status`, `total_price`) VALUES (9, '已支付', 110.00);
INSERT INTO order_info (order_id, product_id, quantity, unit_price) VALUES (LAST_INSERT_ID(), 1, 2, 50.00);

-- 删除操作
DELETE FROM `order` WHERE `order_id` = 11;

-- 检查order_info表，确保与刚才删除的订单相关的记录已被删除。
SELECT * FROM order_info WHERE order_id = 11;