22. Mysql 触发器

概念

触发器（Trigger）是 Mysql 数据库中的一种特殊的存储过程，它会在数据库中的指定表上自动执行某些操作。当满足触发器定义的条件时，触发器会被触发并执行相应的操作。触发器可以用于实现数据的自动更新、数据的完整性约束等功能。

应用场景：

• 数据完整性约束：通过触发器可以实现对表中数据的完整性约束，例如，可以在插入或更新数据时自动检查数据的合法性。
• 数据同步：可以在一个表上的操作触发时，自动将数据同步到其他相关的表中。
• 日志记录：可以在对表进行插入、更新或删除操作时，自动记录操作日志。
• 数据备份：可以在对表进行插入、更新或删除操作时，自动将数据备份到其他表或文件中。

常见操作

查看触发器

使用 show 或者 select 语句查看当前数据库中所有的触发器。

show triggers;
select * from information_schema.triggers;;

创建触发器

使用 create trigger 语句来创建一个触发器，语法如下：

create
    [definer = user]
    trigger [if not exists] trigger_name
    trigger_time trigger_event
    on tbl_name for each row
    [trigger_order]
    trigger_body

trigger_time: { before | after }

trigger_event: { insert | update | delete }

trigger_order: { follows | precedes } other_trigger_name

• trigger_name：触发器的名称。
• trigger_time：指定触发器的执行时间，可以是 before 或 after。
• trigger_event：指定触发器的事件，可以是 insert、update 或 delete。
• table_name：指定触发触发器的表名。
• for each row：固定语法，表示触发器的每一行数据都会被触发。
• trigger_order：可选，指定触发器顺序，可以是 follows(之后) 或 precedes(之前)激活。
• trigger_body：触发器的具体执行逻辑，如果包含多条语句，可以使用 begin … end 复合结构。

删除触发器

使用 drop trigger 语句删除该触发器。

drop trigger [if exists] [schema_name.]trigger_name;

其中，schema_name 是数据库的名称，可选项，trigger_name是要删除的触发器的名称。

精选示例

数据完整性约束

准备工作：新建一个学生表和成绩表，创建一个触发器，用于在插入成绩数据时自动检查成绩是否合法。

create table if not exists students (
    id int primary key,
    name varchar(100),
    age int,
    gender varchar(10)
);
create table if not exists scores (
    id int primary key,
    student_id int,
    subject varchar(100),
    score int,
    foreign key (student_id) references students(id)
);
# 创建触发器
create trigger check_score
before insert on scores
for each row
begin
    if new.score < 0 or new.score > 100 then
        signal sqlstate '45000' set message_text = 'invalid score';
    end if;
end;

触发器逻辑是插入成绩数据之前检查成绩是否合法，如果成绩小于0或大于100，则触发器会抛出一个异常。

数据同步

准备工作：新建订单表和库存表，创建一个触发器，当发生订单销售记录时，更新库存记录。

create table if not exists orders (
    id int primary key,
    product_id int,
    quantity int
);
create table if not exists inventory (
    id int primary key,
    product_id int,
    quantity int
);
# 创建触发器
create trigger update_inventory
after insert on orders
for each row
begin
    update inventory set quantity = quantity - new.quantity where product_id = new.product_id;
end;

触发器会自动在插入订单数据之后自动更新库存表中的库存数量，实施数据同步更新操作。

日志记录

准备工作：假设有文章表和文章日志表，创建一个触发器，用于在更新文章数据时自动记录更新日志。

create table articles (
    id int primary key,
    title varchar(100),
    content text
);
create trigger log_update
after update on articles
for each row
begin
    insert into article_logs (article_id, action, updated_at) values (old.id, 'update', now());
end;

这个触发器会在更新文章数据之后自动将更新日志插入到文章日志表中。

总结

触发器也可以提高数据库的自动化程度，减少手动操作的工作量。也可以帮助我们记录操作日志，具体记录什么时间进行了什么操作。这可以帮助我们更好地还原操作时的具体场景，更好地定位到问题原因所在。

参考资料

• MySQL官方文档：https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html
• MySQL Tutorial 官方文档：https://www.mysqltutorial.org/mysql-trigger/