介绍

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。
触发器的特性：
1、有begin end体，begin end;之间的语句可以写的简单或者复杂
2、什么条件会触发：I、D、U
3、什么时候触发：在增删改前或者后(before/after)
4、触发频率：针对每一行执行
5、触发器定义在表上，附着在表上。
也就是由事件来触发某个操作，事件包括INSERT语句，UPDATE语句和DELETE语句；可以协助应用在数据库端确保数据的完整性。

语法

CREATE TRIGGER trigger_name
trigger_time: { BEFORE | AFTER }
ON tbl_name FOR EACH ROW
trigger_body
trigger_event: { INSERT | UPDATE | DELETE }

变量类型

类型	NEW和OLD使用
INSERT	NEW变量，获取Insert后的数据。
update	NEW变量，获取update后的数据；OLD变量，获取update前的数据。
delete	OLD变量，获取删除前数据

创建insert触发器

建表

CREATE TABLE t4_log(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
act_user VARCHAR(64),
act_type VARCHAR(50) ,
act_time VARCHAR(50),
act_id VARCHAR(20),
act_comment VARCHAR(100));

创建触发器

当t4表有新数据插入的时候，t4_log表会记录操作信息。

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `world`.`t_t4` AFTER INSERT
    ON `world`.`t4`
    FOR EACH ROW 
    BEGIN
    INSERT INTO t4_log(act_user,act_type,act_time,act_id,act_comment) 
    VALUES (USER(),'insert',NOW(),new.id,
    CONCAT('insert into t4 values(',new.id,',',new.name,',',new.age,',',new.gender,');'));
    

    END$$

DELIMITER ;

查看触发器

mysql> show triggers;
+---------+--------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+----------+---------------+----------------------+----------------------+--------------------+
| Trigger | Event  | Table | Statement                                                                                                                                                                                                                      | Timing | Created                | sql_mode | Definer       | character_set_client | collation_connection | Database Collation |
+---------+--------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+----------+---------------+----------------------+----------------------+--------------------+
| t_t4    | INSERT | t4    | BEGIN
    insert into t4_log(act_user,act_type,act_time,act_id,act_comment) 
    values (user(),'insert',now(),new.id,
    concat('insert into t4 values(',new.id,',',new.name,',',new.age,',',new.gender,');'));
    
    END | AFTER  | 2020-06-15 04:08:49.51 |          | root@10.0.0.% | utf8                 | utf8_general_ci      | utf8mb4_0900_ai_ci |
+---------+--------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+----------+---------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

插入数据

存储过程如下：

DELIMITER $$

USE `world`$$

DROP PROCEDURE IF EXISTS `p_iterate`$$

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_iterate`( IN num INT)
BEGIN
    
    DECLARE u_n VARCHAR(64);
    DECLARE u_a TINYINT;
    DECLARE u_g CHAR(1);
    DECLARE i INT DEFAULT 1;
    
    lab1: WHILE i < num
    DO
    SELECT SUBSTR(REPLACE(UUID(),'-',''),1,6) INTO u_n;
    SELECT CEIL(RAND()*18)+17 INTO u_a;
    SELECT SUBSTR('MF',CEIL(RAND()*2),1) INTO u_g;
    SET i = i+1;
    IF MOD(i,2)=0 
    THEN
    ITERATE lab1;
    ELSE
    INSERT INTO t4(NAME,age,gender) VALUES(CONCAT(u_n,'_',i),u_a,u_g);
    END IF;
    END WHILE lab1;
    END$$

DELIMITER ;

调用

TRUNCATE TABLE t4;

CALL p_iterate(100)
SELECT * FROM t4;

SELECT * FROM t4_log;

delete触发器

创建触发器

DELIMITER $$

USE `world`$$

DROP TRIGGER /*!50032 IF EXISTS */ `t_delete`$$

CREATE
    /*!50017 DEFINER = 'root'@'10.0.0.%' */
    TRIGGER `t_delete` BEFORE DELETE ON `t4` 
    FOR EACH ROW BEGIN
    INSERT INTO t4_log(act_user,act_type,act_time,act_id,act_comment)
    VALUES(USER(),'delete',NOW(),old.id,
    CONCAT('delete from t4 where id=',old.id,';'));
    END;
$$

DELIMITER ;

调用

DELETE FROM t4 WHERE id=5;
SELECT * FROM t4_log;

 

 

update触发器

修改表结构（添加一个记录反向操作的字段）

mysql> alter table t4_log add column act_rev varchar(64);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t4_log
    -> ;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| act_user    | varchar(64)  | YES  |     | NULL    |                |
| act_type    | varchar(50)  | YES  |     | NULL    |                |
| act_time    | varchar(50)  | YES  |     | NULL    |                |
| act_id      | varchar(20)  | YES  |     | NULL    |                |
| act_comment | varchar(100) | YES  |     | NULL    |                |
| act_rev     | varchar(64)  | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

创建触发器

DELIMITER $$

USE `world`$$

DROP TRIGGER /*!50032 IF EXISTS */ `t_update`$$

CREATE
    /*!50017 DEFINER = 'root'@'10.0.0.%' */
    TRIGGER `t_update` AFTER UPDATE ON `t4` 
    FOR EACH ROW BEGIN
    INSERT INTO t4_log(act_user,act_type,act_time,act_id,act_comment,act_rev)
    VALUES (USER(),'update',NOW(),old.id,
    CONCAT('update t4 set name=\'',new.name,'\',age=',new.age,',gender=\'',new.gender,'\' where id=',new.id),
    CONCAT('update t4 set name=\'',old.name,'\',age=',old.age,',gender=\'',old.gender,'\' where id=',old.id));
    END;
$$

DELIMITER ;

查一下更新前的值

mysql> select * from t4 where id=21;
+----+-----------+-----+--------+
| id | name      | age | gender |
+----+-----------+-----+--------+
| 21 | 7ec897_43 |  30 | F      |
+----+-----------+-----+--------+
1 row in set (0.00 sec)

更新表

mysql> update t4 set name='tom',age=13,gender='F' where id=21;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看表数据

mysql> select * from t4_log;
+----+----------------+----------+---------------------+--------+--------------------------------------------------------+--------------------------------------------------------------+
| id | act_user       | act_type | act_time            | act_id | act_comment                                            | act_rev                                                      |
+----+----------------+----------+---------------------+--------+--------------------------------------------------------+--------------------------------------------------------------+
|  1 | root@localhost | update   | 2020-06-15 11:31:03 | 21     | update t4 set name='tom',age=13,gender='F' where id=21 | update t4 set name='7ec897_43',age=30,gender='F' where id=21 |
+----+----------------+----------+---------------------+--------+--------------------------------------------------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

验证逆向操作

mysql> update t4 set name='7ec897_43',age=30,gender='F' where id=21;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t4 where id=21;
+----+-----------+-----+--------+
| id | name      | age | gender |
+----+-----------+-----+--------+
| 21 | 7ec897_43 |  30 | F      |
+----+-----------+-----+--------+
1 row in set (0.00 sec)

应用中的示例：商品库存自动更新

商品信息表

create table goods(
id int primary key auto_increment,
name varchar(20) not null,
price decimal(10,2) default 1,
inv int comment '库存数量');

insert into goods
values
(null,'华为',11999,1000),
(null,'苹果',15999,50),
(null,'惠普',5999,2000),
(null,'小米',10999,2500),
(null,'戴尔',6999,3000);

订单表

create table orders(
id int primary key auto_increment,
o_id int not null comment '商品id',
o_number int comment '商品数量'
) ;

创建触发器

create trigger after_order
after insert on orders
for each row
begin
update goods set inv = inv - new.o_number where id = new.id;
end
insert into orders(o_id,o_number) values(1,1,3);