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

触发器

1、用户购买商品时，要求库存表中的库存数量自动修改

详细示例

delimiter $$
create trigger stock_change
after -- 事件触发在 下订单之后
insert -- 监视插入事件
on stock -- 监视 order订单表
for each row
begin
update stock set stock=stock-new.stock_qty where stock_id= new.stock_id; -- 注意这里是 old.id=new.tid
end 
$$
delimiter ;

 测试语句

-- 插入一些示例数据到stock表  
INSERT INTO stock  VALUES (10,'978-10',333,'2021-01-02 12:01:02');
  
-- 插入一个订单到order表，这将触发stock_change触发器  
INSERT INTO `order` (stock_id, quantity) VALUES (1, 10);  
  
-- 检查stock表以确认库存数量已经更新  
SELECT * FROM stock;

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

详细示例

-- 创建一个名为books_check_price_change 的触发器
DELIMITER $$
-- 创建触发器，命名为books_check_price_change
 
-- 在books表的每一行数据更新之前执行此触发器
CREATE TRIGGER books_check_price_change 
BEFORE UPDATE ON books -- 在books表的每一行数据更新之前执行此触发器
FOR EACH ROW
begin 
	-- 编写代码的区域
	-- 声明一个变量来存储商品旧的价格
    DECLARE old_bprice DECIMAL(10, 2);
    -- 声明一个变量来存储商品新的价格
    DECLARE new_bprice DECIMAL(10, 2);
    -- 声明一个变量来存储价格变动的百分比
    DECLARE percentage_change DECIMAL(10, 2);
		
    -- 将旧的价格值赋给old_price变量，这里的OLD是MySQL提供的关键字，用于获取更新前的字段值
    SET old_bprice = OLD.bprice;
    -- 将新的价格值赋给new_price变量，NEW关键字用于获取更新后的字段值
    -- 如果是，则触发一个错误信号，并返回指定的错误消息
SET new_bprice = NEW.bprice;
    
    -- 接下来检查新的价格是否为0，因为商品的价格通常不应该为0
 if new.bprice=0 then
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '新价格不能为0。';
	end if;
	-- 题目要求加上上下浮动不得超过10%
	-- 计算价格变动的百分比
    -- 使用公式：(新价格 - 旧价格) / 旧价格 * 100
    -- 这里需要确保old_price不为0，否则除以0会导致错误
	set percentage_change = (new.bprice-old.bprice)/old.bprice*100;
	if abs(percentage_change)>10 then
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '价格上下浮动不得超过10%。';
		
	end if;
	-- 触发器执行完毕
end

 

 测试语句：

-- 正常更新（未超过10%）：
UPDATE books SET bprice = 50 WHERE bookname = '遨游神秘洋'; -- 应成功
 
-- 更新超过10%（触发错误）：
UPDATE books SET bprice = 10 WHERE bookname = '遨游神秘洋'; -- 应触发错误

 

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

详细示例 

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

 测试语句

DELETE FROM shipped WHERE `ship_id` = 2;

SELECT *	from shipped where ship_id=2;