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