对触发器用其来实现数据完整性举出两个业务规则例子并使用触发器加以实现

业务规则例子1：用户注册时，用户名不能重复 触发器实现：

CREATE TRIGGER check_duplicate_username BEFORE INSERT ON users FOR EACH ROW BEGIN DECLARE msg VARCHAR(255); IF EXISTS(SELECT * FROM users WHERE username = NEW.username) THEN SET msg = CONCAT('Username "', NEW.username, '" already exists'); SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; END IF; END;

业务规则例子2：订单总金额不能超过客户的信用额度 触发器实现：

CREATE TRIGGER check_credit_limit BEFORE INSERT ON orders FOR EACH ROW BEGIN DECLARE msg VARCHAR(255); DECLARE total_amount DECIMAL(10,2); SELECT SUM(quantity*price) INTO total_amount FROM order_items WHERE order_id = NEW.id; IF total_amount > (SELECT credit_limit FROM customers WHERE id = NEW.customer_id) THEN SET msg = CONCAT('Order total amount (', total_amount, ') exceeds credit limit'); SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; END IF; END