sql触发器限制修改和插入条件

创建触发器，限制对表进行insert和update操作时输入的数据大于某个值

**1、插入操作：销售总额不能超过5000
**

DELIMITER 
CREATE TRIGGER validate_sales_sumamount
BEFORE INSERT
ON t_sales
FOR EACH ROW
IF NEW.sales_total_amount>5000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "你输入的销售总额超过5000元，操作失败！";
END IF
DELIMITER ;
-- 测试插入结果
Insert into  t_sales(sales_id, customer_id, sales_amount) values('1','1','8800');

2、更新操作：不能给vip用户降级

DELIMITER 
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON t_customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'VIP客户不能降级为普通用户';
END IF 
DELIMITER ;
-- 测试更新语句
Update t_customers set level='BASIC' where customer_id='1';