第十章《触发器》

Java架构师必看

发布于 2021-06-02 09:57:38

2560

发布于 2021-06-02 09:57:38

触发器（trigger）是一个特殊的存储过程，都是嵌入到mysql的一段程序，触发器是由事件来出发某个操作，触发器涉及到sql语句是DML（insert、update、delete）。触发器触发的操作里面可以是单条sql语句也可以是多条sql语句的集合，不需要手动启动，只要当一个预定义事件发生，就会被mysql自动调用执行预先定义好的操作。注意：尽量少用触发器，因为触发器执行速度比较慢，如果滥用触发器，会严重影响sql语句执行的效率。

简单理解：你执行一条sql语句，这条sql语句的执行会自动触发执行其他的sql语句

创建触发器的四个要素：（1）监视地点：（table）（2）监视事件：（insert、update、delete）（3）触发时间：（before/after）（4）触发事件：（insert、update、delete）

创建触发器的语法： create trigger 触发器名触发时间（before/after）触发事件（insert、update、delete） on 表名 for each row 要执行的SQL语句（单个SQL语句/begin 多个SQL语句 end）；

delimiter 定界符号；分隔符号其实就是告诉MySQL解释器，该段命令是否已经结束了，mysql是否可以执行了。

单条：

mysql> create table log1(log varchar(255) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> create table time(sql_time timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger log_time before insert on log1mysql> create trigger log_time befues(now());
Query OK, 0 rows affected (0.02 sec)

mysql> insert into log1 values('create table test3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from time;
+---------------------+
| sql_time            |
+---------------------+
| 2020-04-02 09:19:54 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into log1 values('create table test4');
Query OK, 1 row affected (0.01 sec)

mysql> select * from time;
+---------------------+
| sql_time            |
+---------------------+
| 2020-04-02 09:19:54 |
| 2020-04-02 09:20:57 |
+---------------------+
2 rows in set (0.00 sec)

多条：

mysql> create table test1(a1 int);
Query OK, 0 rows affected (0.04 sec)

mysql> create table test2(a1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> drop tables test2;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test2(a2 int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table test3(a3 int auto_increment primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> create table test4(a4 int not null auto_increment primary key,b4 int default 0); 
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> create trigger test_ref after insert on test1 for each row begin insert into test2 set a2=new.a1;
    -> delete from test3 where a3=new.a1;
    -> update test4 set b4=b4+1 where a4=new.a1;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test3(a3 INT AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,b4 INT DEFAULT 0);
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //
mysql> CREATE TRIGGER test_ref AFTER INSERT ON test1 FOR EACH ROW 
    -> BEGIN
    -> INSERT INTO test2 SET a2=NEW.a1;
    -> DELETE FROM test3 WHERE a3=NEW.a1;
    -> UPDATE test4 SET b4=b4+1 WHERE a4=NEW.a1;
    -> END//
mysql> DELIMITER ；
给test3和4插入基础数据：
mysql> INSERT INTO test3 VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.00 sec)



mysql> INSERT INTO test4(a4) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    0 |
|  2 |    0 |
|  3 |    0 |
|  4 |    0 |
|  5 |    0 |
|  6 |    0 |
|  7 |    0 |
|  8 |    0 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)
给test1插入数据触发触发器：
mysql> INSERT INTO test1 VALUES(1),(3),(1),(4),(7),(8),(8),(5),(4);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    4 |
|    7 |
|    8 |
|    8 |
|    5 |
|    4 |
+------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  6 |
|  9 |
| 10 |
+----+
4 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    2 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    1 |
|  6 |    0 |
|  7 |    1 |
|  8 |    2 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)

NEW和OLD解释： mysql中定义了new和old，用来表示触发器的所在表中，触发了触发器的哪一行数据，来引用触发器中发生变化的记录内容，具体的：（1）在insert型触发器中，new用来表示将要（before）或者已经（after）插入的新数据；（2）在update型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改完成的新数据；（3）在delete型触发器中，OLD用来表示将要或已经被删除的原数据使用方法： NEW.字段名 OLD.字段名

查看触发器 1.SHOW TRIGGERS\G 2.触发器的信息记录在information_schema.triggers 这个表里。我们select这个表就可以了

删除触发器： DROP TRIGGER 触发器名称

mysql> CREATE TABLE orders (o_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
g_id INT NOT NULL DEFAULT 0,num INT NOT NULL DEFAULT 0);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE goods(id INT AUTO_INCREMENT PRIMARY KEY,goods_name VARCHAR(30) NOT NULL,goods_num INT NOT NULL DEFAULT 0);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO goods(goods_name,goods_num) VALUES('手机',100),('电 脑',20),
('洗发水',300),('书',30);

mysql> CREATE TRIGGER kucun AFTER INSERT ON orders FOR EACH ROW UPDATE 
goods SET goods_num=goods_num-NEW.num WHERE id=NEW.g_id;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO orders(g_id,num) VALUES(1,2),(3,100),(4,5);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM orders;
+------+------+-----+
| o_id | g_id | num |
+------+------+-----+
|    1 |    1 |   2 |
|    2 |    3 | 100 |
|    3 |    4 |   5 |
+------+------+-----+
3 rows in set (0.00 sec)

mysql> SELECT * FROM goods;
+----+------------+-----------+
| id | goods_name | goods_num |
+----+------------+-----------+
|  1 | 手机       |        98 |
|  2 | 电脑       |        20 |
|  3 | 洗发水     |       200 |
|  4 | 书         |        25 |
+----+------------+-----------+
4 rows in set (0.00 sec)

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

sql