MySQL触发器详细介绍和使用

什么是触发器

MySQL触发器（MySQL triggers）是一种数据库对象，它与表相关联，并在表中的某个事件发生时自动执行一系列操作。触发器可以在以下几种事件发生时触发：

1. 插入（INSERT）：当向表中插入新行时触发触发器。
2. 更新（UPDATE）：当更新表中的行时触发触发器。
3. 删除（DELETE）：当从表中删除行时触发触发器。

触发器作用

1. 强制业务规则：通过在触发器中编写逻辑，可以在特定的表上自动执行业务规则。触发器可以检查输入的数据是否符合要求，限制某些操作的执行，确保数据的完整性和一致性。
2. 记录数据变更：触发器可以用于自动记录数据的变更情况。通过在触发器中编写逻辑，可以在特定的表上记录数据的修改时间、修改人等信息，提供数据的审计和跟踪功能。
3. 复杂默认值计算：通过在触发器中编写逻辑，可以在特定的表上自动计算默认值。触发器可以根据其他字段的值计算出一个新字段的值，并将其设置为默认值，简化应用程序的开发和维护。
4. 数据同步：触发器可以用于在多个表之间自动同步数据。当一个表中的数据发生变化时，可以在触发器中编写逻辑，自动在其他相关表中进行相应的插入、更新或删除操作，确保数据的一致性。
5. 数据校验：触发器可以用于自动校验数据的正确性。通过在触发器中编写逻辑，可以检查数据的唯一性、完整性或其他业务规则，并阻止不符合规则的操作执行，提高数据的质量和准确性。

触发器的组成

1. 事件（Event）：触发器关联的数据库表上的特定操作，如INSERT、UPDATE或DELETE。
2. 条件（Condition）：定义触发器执行的条件。只有当条件满足时，触发器才会被触发执行，（新增前后：before insert、after insert，更新前后：before update、 after update，删除前后：before delete 、 after delete）。
3. 动作（Action）：触发器要执行的一系列SQL语句。可以是单个语句或复杂的语句块。

当满足触发器定义的事件和条件时，MySQL会自动执行触发器的动作。触发器可以在表级别上定义，这意味着每当相关操作发生时，无论是通过应用程序、命令行工具还是其他方式，触发器都会被触发执行。

具体使用

准备数据

场景：当用户表新增或者删除的时候，在记录表中增加一条数据

创建用户表

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `user_name` varchar(64) NOT NULL COMMENT '用户名',
  `password` varchar(64) NOT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

创建用户操作记录表

CREATE TABLE `user_operation_record` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `action` tinyint NOT NULL COMMENT '动作, 1:新增 0:删除',
  `user_name` varchar(64) NOT NULL COMMENT '用户名',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间;默认当前时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户操作记录表';

添加触发器

当用户表新增后，用户记录表添加一条记录

-- 创建一个触发器，当向user表中插入一条记录后，自动向user_operation_record表中插入一条记录
create trigger user_insert_trigger
    after insert
    on user
    for each row
begin
    insert into user_operation_record(action, user_name)
    values (1, New.user_name);
end;

当用户表删除后，用户记录表添加一条记录

-- 创建一个触发器，当向user表中删除一条记录后，自动向user_operation_record表中插入一条记录
create trigger user_delete_trigger
    after delete
    on user
    for each row
begin
    insert into user_operation_record(action, user_name)
    values (0, OLD.user_name);
end;

操作用户表

-- 新增两条数据
insert into user (user_name, password) values ('curry', '12345');
insert into user (user_name, password) values ('james', '12345');
-- 删除一条数据
delete from user where user_name = 'curry';

查询用户操作记录表，看下是否有数据

select * from user_operation_record;

有数据，说明触发器生效

查看触发器

-- 查看触发器
SHOW TRIGGERS
-- 根据触发器名称查看触发器
SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';
SELECT * FROM information_schema.triggers WHERE trigger_name= 'user_delete_trigger';

触发器删除

-- 删除触发器
DROP TRIGGER [IF EXISTS][数据库.]触发器名称;
-- 删除名称为‘user_insert_trigger’的触发器
drop trigger user_insert_trigger;