简介:
MySQL 触发器
这次实验是在一台 MySQL Slave 上进行的,事实证明:从库添加数据库、表、插入、删除数据等,不会导致主从失败。
一、创建实验数据库、表
mysql > create database trdb default character setutf8;
mysql> create table trdb.t_film (id int(5) primary key auto_increment, name varchar(32), cid int(3), status int(1));
mysql> desctrdb.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> desctrdb.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 > usetrdb;
mysql>delimiter $
# 定义界定符,默认 ;
1、INSERT 事件
mysql > create trigger tr_insert after insert on t_film foreach 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 foreach 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 foreach 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;endTiming: AFTER
Created:NULLsql_mode:
Definer: root@localhostcharacter_set_client: utf8
collation_connection: utf8_general_ciDatabaseCollation: 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;endTiming: AFTER
Created:NULLsql_mode:
Definer: root@localhostcharacter_set_client: utf8
collation_connection: utf8_general_ciDatabaseCollation: 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;endTiming: AFTER
Created:NULLsql_mode:
Definer: root@localhostcharacter_set_client: utf8
collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci
三、验证触发器
mysql > select * fromt_film;
Emptyset (0.00sec)
mysql> select * fromt_tr;
Emptyset (0.00 sec)
1、INSERT 事件验证
mysql > insert into t_film set name = '警察故事 1', cid = 2, status = 1;
mysql> select * fromt_film;+----+----------------+------+--------+
| id | name | cid | status |
+----+----------------+------+--------+
| 1 | 警察故事 1 | 2 | 1 |
+----+----------------+------+--------+
mysql> select * fromt_tr;+----+------+----------+
| id | vid | opertion |
+----+------+----------+
| 1 | 1 | 1 |
+----+------+----------+
# 触发器达到效果,数据被写入
mysql > insert into t_film set name = '警察故事 2', cid = 41, status = 0;
mysql> select * fromt_film;+----+----------------+------+--------+
| id | name | cid | status |
+----+----------------+------+--------+
| 1 | 警察故事 1 | 2 | 1 |
| 2 | 警察故事 2 | 41 | 0 |
+----+----------------+------+--------+
mysql> select * fromt_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 * fromt_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 * fromt_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 * fromt_tr;+----+------+----------+
| id | vid | opertion |
+----+------+----------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 1 | 3 |
+----+------+----------+
# 触发器达到效果,只有删除 警察故事 1 的事件被记录
4、批量操作
mysql > select * fromt_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 * fromt_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 * fromt_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 fromt_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 |
+----+------+----------+
# 批量操作也没有问题!