简介：
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 |
+----+------+----------+
# 批量操作也没有问题！