mysql trigger 触发器_创建-查看-删除

0. 创建两个测试用的表

-- 使用 test 数据库
USE test;

-- 使用字符集 utf8mb4
SET NAMES utf8mb4;

-- 创建 dogs 表
CREATE TABLE `dogs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `string` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'string',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
);

-- 创建记录触发器操作日志的表
CREATE TABLE `dogs_tri` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `log` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'log',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
);

1. mysql 创建触发器

DELIMITER $$  --设置结束符为 $$

-- 插入数据之前的触发器 BEFORE INSERT
CREATE TRIGGER ins_dogs_bef BEFORE INSERT ON dogs FOR EACH ROW 
BEGIN
    INSERT INTO dogs_tri (log) VALUES ('before insert into dogs');
END;

-- 插入数据之后的触发器 AFTER INSERT
CREATE TRIGGER ins_dogs_aft AFTER INSERT ON dogs FOR EACH ROW 
BEGIN
    INSERT INTO dogs_tri (log) VALUES ('after insert into dogs');
END;

-- 更新数据之前的触发器 BEFORE UPDATE
CREATE TRIGGER up_dogs_bef BEFORE UPDATE ON dogs FOR EACH ROW 
BEGIN
    INSERT INTO dogs_tri (log) VALUES ('BEFORE UPDATE into dogs');
END;

-- 更新数据之后的触发器 AFTER UPDATE
CREATE TRIGGER up_dogs_aft AFTER UPDATE ON dogs FOR EACH ROW 
BEGIN
    INSERT INTO dogs_tri (log) VALUES ('AFTER UPDATE into dogs');
END;

$$

DELIMITER ;  -- 还原结束符为分号 ;

2. 查看触发器

show triggers ;

3. 删除触发器

-- 根据触发器的名字删除
drop trigger up_dogs_aft ;

或者

select * from information_schema.triggers where trigger_name = 'up_dogs_aft' ;

参考：《深入浅出MySQL》第二版 13.1