如何有效地使用MySQL数据库触发器来自动化数据操作？

MySQL数据库触发器是存储在数据库中的一种特殊类型的存储过程，当特定的事件（如INSERT、UPDATE或DELETE）发生在表上时，会自动执行。

题目描述

假设有一个“富豪榜”和一个“平民榜”，每个表都有id、姓名和身价，当一个平民的身价达到50万时，他应该晋级为富豪，但如果平民的身价突然增加到200万以上，系统应怀疑其金钱来源不明并抛出异常，请创建一个触发器来实现这个功能。

创建表格

1、创建富豪表

CREATE TABLE wealthy (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age INT,
    name VARCHAR(20),
    money INT
);

2、创建平民表

CREATE TABLE person (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age INT,
    name VARCHAR(20),
    money INT
);

3、插入初始数据

 插入富豪榜数据
INSERT INTO wealthy (age, name, money) VALUES (20, '张三', 110);
INSERT INTO wealthy (age, name, money) VALUES (35, '李四', 110);
INSERT INTO wealthy (age, name, money) VALUES (35, '王五', 90);
INSERT INTO wealthy (age, name, money) VALUES (20, '赵六', 90);
 插入平民榜数据
INSERT INTO person (age, name, money) VALUES (20, '十一', 30);

创建触发器

创建在更新平民表后执行的触发器，检查身价变化是否合法。

DELIMITER $$
CREATE TRIGGER person_trigger_update_after 
AFTER UPDATE ON person FOR EACH ROW
BEGIN
    IF NEW.money > 200 THEN
        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = 'error money';
    ELSEIF NEW.money > 50 THEN
        INSERT INTO wealthy (age, name, money) VALUES (OLD.age, OLD.name, NEW.money);
    END IF;
END;
$$
DELIMITER ;

测试触发器

1、更新平民表，使身价超过200万

UPDATE person SET money = 210 WHERE id = 1;

结果会报错：ERROR 1644 (HY000): error money。

2、更新平民表，使身价超过50万但不超过200万

UPDATE person SET money = 80 WHERE id = 1;

查看富豪表：

SELECT * FROM wealthy;

可以看到，新的记录已被成功插入到富豪表中。

查看和删除触发器

1、查看触发器

SHOW TRIGGERS;

2、删除触发器

DROP TRIGGER person_trigger_update_after;

通过这个例子，可以了解到MySQL触发器的基本使用方法及其在实际应用中的作用，触发器可以帮助我们自动维护数据的一致性和完整性，同时实现一些复杂的业务逻辑，在实际应用中，可以根据具体需求灵活使用触发器来简化操作和管理。