mysql15-触发器
- 测试数据
create database hh; use hh; create table tb1( id int primary key auto_increment, name char(15), sex enum('男','女') ); create table add2( id int primary key auto_increment, name char(15), sex enum('男','女') ); create table del3( id int primary key auto_increment, name char(15), sex enum('男','女') ); create table up4( id int primary key auto_increment, old_name char(15), new_name char(15) );
1、创建触发器
- 触发器(trigger)是个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MysQL自动调用。
- 触发器是由事件来触发某个操作，这些事件包括INSERT，UPDATAE和DELETE语句。
- 如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。
- 触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。例如:可以根据客户当前的账户状态，控制是否允许插入新订单。
- 触发器中预定义了两个变量，可以用来引用变更的字段值。
- new：用来引用新传入的字段值。
- old：用来引用表中存在的字段值。
- 基本语法格式如下：
CREATE
[DEFINER = user]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
-
- trigger_name标识触发器名称，用户自行指定。
- trigger_time标识触发时机，可以指定为before或after。
- trigger_event标识触发事件，包括INSERT、UPDATE和DELETE。
- tbl_name标识建立触发器的表名，即在哪张表上建立触发器。
- trigger_body是触发器执行语句。
- 只需要执行一条SQL语句，可以直接放在此处即可。
- 需要执行多条SQL语句，必须放在BEGIN...END之间。
示例1：创建只有一个执行语句的触发器
delimiter //
CREATE TRIGGER trigger1
BEFORE INSERT
ON tb1 FOR EACH ROW
insert into add2(name,sex) values(new.name,new.sex); --new引用insert传入的字段值
//
delimiter ;
mysql> insert into tb1(name,sex) values('张三','男');
mysql> insert into tb1(name,sex) values('李四','男');
mysql> insert into tb1(name,sex) values('王二','男'),('麻子','男'),('翠花','女'),('小红','女');
mysql> select * from tb1;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | 张三 | 男 |
| 2 | 李四 | 男 |
| 3 | 王二 | 男 |
| 4 | 麻子 | 男 |
| 5 | 翠花 | 女 |
| 6 | 小红 | 女 |
+----+------+------+
mysql> select * from add2;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | 张三 | 男 |
| 2 | 李四 | 男 |
| 3 | 王二 | 男 |
| 4 | 麻子 | 男 |
| 5 | 翠花 | 女 |
| 6 | 小红 | 女 |
+----+------+------+
示例2：创建有多个执行语句的触发器
delimiter //
CREATE TRIGGER trigger2
BEFORE DELETE
ON tb1 FOR EACH ROW
BEGIN --BEGIN...END之间可写多条SQL语句
insert into del3(name,sex) values(old.name,old.sex); --old用来引用表中原先存在的字段值
END //
delimiter ;
mysql> delete from tb1 where id = 1;
mysql> delete from tb1 where sex = '女';
mysql> select * from tb1;
+----+------+------+
| id | name | sex |
+----+------+------+
| 2 | 李四 | 男 |
| 3 | 王二 | 男 |
| 4 | 麻子 | 男 |
+----+------+------+
mysql> select * from del3;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | 张三 | 男 |
| 2 | 翠花 | 女 |
| 3 | 小红 | 女 |
+----+------+------+
示例3：
delimiter //
CREATE TRIGGER trigger3
BEFORE UPDATE
ON tb1 FOR EACH ROW
BEGIN --BEGIN...END之间可写多条SQL语句
insert into up4(old_name,new_name) values(old.name,new.name);
END //
delimiter ;
mysql> update tb1 set name = '王八' where name = '王二';
mysql> select * from tb1;
+----+------+------+
| id | name | sex |
+----+------+------+
| 2 | 李四 | 男 |
| 3 | 王八 | 男 |
| 4 | 麻子 | 男 |
+----+------+------+
mysql> select * from up4;
+----+----------+----------+
| id | old_name | new_name |
+----+----------+----------+
| 1 | 王二 | 王八 |
+----+----------+----------+
2、查看触发器
1、SHOW TRIGGERS语句查看触发器信息
- 基本语法格式如下：
SHOW TRIGGERS;
-
- 在触发器较少的情况下，使用该语句会很方便。
示例：
mysql> show triggers\G
*************************** 1. row ***************************
Trigger: trigger3
Event: UPDATE
Table: tb1
Statement: BEGIN
insert into up4(old_name,new_name) values(old.name,new.name);
END
Timing: BEFORE
Created: 2021-09-28 17:43:59.68
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: utf8_general_ci
......
2、在triggers表中查看触发器信息
- 在MySQL中所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表中。
- 基本语法格式如下：
SELECT FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;
示例：
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name LIKE 'trigger3'\G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: hh
TRIGGER_NAME: trigger3
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: hh
EVENT_OBJECT_TABLE: tb1
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
insert into up4(old_name,new_name) values(old.name,new.name);
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2021-09-28 17:43:59.68
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci
DATABASE_COLLATION: utf8_general_ci
3、删除触发器
- 基本语法格式如下：
DROP TRIGGER [database_name.]trigger_name;
示例：
DROP TRIGGER trigger1;
