MySQL 触发器示例


简介：

MySQL 触发器

这次实验是在一台 MySQL Slave 上进行的，事实证明：从库添加数据库、表、插入、删除数据等，不会导致主从失败。

一、创建实验数据库、表

复制代码
mysql > create database trdb default character set utf8;

mysql > create table trdb.t_film (id int(5) primary key auto_increment, name varchar(32), cid int(3), status int(1));

mysql > desc trdb.t_film;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(5)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32) | YES  |     | NULL    |                |
| cid    | int(3)      | YES  |     | NULL    |                |
| status | int(1)      | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

mysql > create table trdb.t_tr (id int(5) primary key auto_increment, vid int(5), opertion int(1));

mysql > desc trdb.t_tr;
+----------+--------+------+-----+---------+----------------+
| Field    | Type   | Null | Key | Default | Extra          |
+----------+--------+------+-----+---------+----------------+
| id       | int(5) | NO   | PRI | NULL    | auto_increment |
| vid      | int(5) | YES  |     | NULL    |                |
| opertion | int(1) | YES  |     | NULL    |                |
+----------+--------+------+-----+---------+----------------+
复制代码

# 表一：t_film 为业务数据表，执行 INSERT、UPDATE、DELETE 操作
# 表二：t_tr 为触发器程序体数据写入表

二、创建触发器

mysql > use trdb;

mysql > delimiter $

# 定义界定符，默认 ;

1、INSERT 事件

复制代码
mysql > create trigger tr_insert after insert on t_film for each row
    -> begin
    -> if new.cid in (2, 3, 4, 5, 6, 45) then
    ->     insert into t_tr set vid = new.id, opertion = 1;
    -> end if;
    -> end$

# 语法介绍：
# 创建一个触发器，名为 tr_insert
# 触发时机，事件发生前 before、事件发生后 after
# 事件类型，insert、update、delete
# 为哪张表创建触发器，t_film ( 同一张表不能同时创建相同类型的触发器 )
# 触发器执行间隔，row 每行触发一次
# begin ... end，区域内可以写逻辑、多条SQL语句

begin
declare uid int(11);
set uid = (select uid from table where uid = new.uid);
if new.uid = uid then
    insert into trdb.tr_t set vid = new.fid, opertion = 1;
end if;
end$

# 变量赋值
# NEW、OLD
# INSERT 触发器时，NEW 表示 BEFORE 将要或 AFTER 已经插入的新数据
# UPDATE 触发器时，OLD 表示 BEFORE 将要或 AFTER 已经被修改的原数据，NEW 表示 BEFORE 将要或 AFTER 已经被修改为的新数据
# DELETE 触发器时，OLD 表示 BEFORE 将要或 AFTER 已经被删除的原数据
复制代码

2、UPDATE 事件

mysql > create trigger tr_update after update on t_film for each row
    -> begin
    -> if new.cid in (2, 3, 4, 5, 6, 45) then
    ->     insert into t_tr set vid = new.id, opertion = 2;
    -> end if;
    -> end$

3、DELETE 事件

mysql > create trigger tr_delete after delete on t_film for each row
    -> begin
    -> if old.cid in (2, 3, 4, 5, 6, 45) then
    ->     insert into t_tr set vid = old.id, opertion = 3;
    -> end if;
    -> end$

mysql > delimiter ;

4、查看触发器

复制代码
mysql > show triggers\G
*************************** 1. row ***************************
             Trigger: tr_insert
               Event: INSERT
               Table: t_film
           Statement: begin
if new.cid in (2, 3, 4, 5, 6, 45) then
    insert into t_tr set vid = new.id, opertion = 1;
end if;
end
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: tr_update
               Event: UPDATE
               Table: t_film
           Statement: begin
if new.cid in (2, 3, 4, 5, 6, 45) then
    insert into t_tr set vid = new.id, opertion = 2;
end if;
end
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 3. row ***************************
             Trigger: tr_delete
               Event: DELETE
               Table: t_film
           Statement: begin
if old.cid in (2, 3, 4, 5, 6, 45) then
    insert into t_tr set vid = old.id, opertion = 3;
end if;
end
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
复制代码

三、验证触发器

mysql > select * from t_film;
Empty set (0.00 sec)

mysql > select * from t_tr;
Empty set (0.00 sec)

1、INSERT 事件验证

复制代码
mysql > insert into t_film set name = '警察故事 1', cid = 2, status = 1;

mysql > select * from t_film;
+----+----------------+------+--------+
| id | name           | cid  | status |
+----+----------------+------+--------+
|  1 | 警察故事 1     |    2 |      1 |
+----+----------------+------+--------+

mysql > select * from t_tr;
+----+------+----------+
| id | vid  | opertion |
+----+------+----------+
|  1 |    1 |        1 |
+----+------+----------+
复制代码

# 触发器达到效果，数据被写入

复制代码
mysql > insert into t_film set name = '警察故事 2', cid = 41, status = 0;

mysql > select * from t_film;
+----+----------------+------+--------+
| id | name           | cid  | status |
+----+----------------+------+--------+
|  1 | 警察故事 1     |    2 |      1 |
|  2 | 警察故事 2     |   41 |      0 |
+----+----------------+------+--------+

mysql > select * from t_tr;
+----+------+----------+
| id | vid  | opertion |
+----+------+----------+
|  1 |    1 |        1 |
+----+------+----------+
复制代码

# 触发器达到效果，数据没有被写入，因为 cid not in (2, 3, 4, 5, 6, 45)

2、UPDATE 事件验证

复制代码
mysql > update t_film set cid = 2, status = 1 where name = '警察故事 2';

mysql > select * from t_tr;
+----+------+----------+
| id | vid  | opertion |
+----+------+----------+
|  1 |    1 |        1 |
|  2 |    2 |        2 |
+----+------+----------+
复制代码

# 触发器达到效果，数据被写入

3、DELETE 事件验证

复制代码
mysql > insert into t_film set name = '警察故事 3', cid = 41, status = 0;

mysql > select * from t_film;
+----+----------------+------+--------+
| id | name           | cid  | status |
+----+----------------+------+--------+
|  1 | 警察故事 1     |    2 |      1 |
|  2 | 警察故事 2     |    2 |      1 |
|  3 | 警察故事 3     |   41 |      0 |
+----+----------------+------+--------+

mysql > delete from t_film where name = '警察故事 1';

mysql > delete from t_film where name = '警察故事 3';

mysql > select * from t_tr;
+----+------+----------+
| id | vid  | opertion |
+----+------+----------+
|  1 |    1 |        1 |
|  2 |    2 |        2 |
|  3 |    1 |        3 |
+----+------+----------+
复制代码

# 触发器达到效果，只有删除 警察故事 1 的事件被记录

4、批量操作

复制代码
mysql > select * from t_film;
+----+----------------+------+--------+
| id | name           | cid  | status |
+----+----------------+------+--------+
|  2 | 警察故事 2     |    2 |      1 |
|  4 | 警察故事 3     |   41 |      0 |
|  5 | 警察故事 4     |   41 |      0 |
|  6 | 警察故事 5     |   41 |      0 |
|  7 | 警察故事 6     |   41 |      0 |
|  8 | 警察故事 7     |   41 |      0 |
|  9 | 警察故事 8     |   41 |      0 |
| 10 | 警察故事 9     |   41 |      0 |
+----+----------------+------+--------+

mysql > select * from t_tr;
+----+------+----------+
| id | vid  | opertion |
+----+------+----------+
|  1 |    1 |        1 |
|  2 |    2 |        2 |
|  3 |    1 |        3 |
+----+------+----------+

mysql > update t_film set cid = 2, status = 1 where cid = 41;

mysql > select * from t_tr;
+----+------+----------+
| id | vid  | opertion |
+----+------+----------+
|  1 |    1 |        1 |
|  2 |    2 |        2 |
|  3 |    1 |        3 |
|  4 |    4 |        2 |
|  5 |    5 |        2 |
|  6 |    6 |        2 |
|  7 |    7 |        2 |
|  8 |    8 |        2 |
|  9 |    9 |        2 |
| 10 |   10 |        2 |
+----+------+----------+

mysql > delete from t_film;

mysql > select * from t_tr where id > 10;
+----+------+----------+
| id | vid  | opertion |
+----+------+----------+
| 11 |    2 |        3 |
| 12 |    4 |        3 |
| 13 |    5 |        3 |
| 14 |    6 |        3 |
| 15 |    7 |        3 |
| 16 |    8 |        3 |
| 17 |    9 |        3 |
| 18 |   10 |        3 |
+----+------+----------+
复制代码

# 批量操作也没有问题！

posted @   WangXiaoQiang  阅读(909)  评论(0)  编辑  收藏  举报
编辑推荐：
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
阅读排行：
· Ollama——大语言模型本地部署的极速利器
· 使用C#创建一个MCP客户端
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· Windows编程----内核对象竟然如此简单？
· ollama系列1：轻松3步本地部署deepseek，普通电脑可用
点击右上角即可分享
微信分享提示
AI IDE Trae