MySQL触发器

介绍

触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。
触发器的特性:
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 }

变量类型

类型 NEWOLD使用
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;

MySQL触发器

 

 

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);

 

MySQL触发器

上一篇:对于关系型数据库而言,索引是相当重要的概念?


下一篇:03-SQLServer导出数据库下的所有存储过程到文本文件