MySQL触发器
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL触发器,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含8161字,纯文字阅读大概需要12分钟。
内容图文
![MySQL触发器](/upload/InfoBanner/zyjiaocheng/873/d06e02e3f3ff4a3cb0b30d0183c26b9d.jpg)
介绍
触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。 触发器的特性: 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);
内容总结
以上是互联网集市为您收集整理的MySQL触发器全部内容,希望文章能够帮你解决MySQL触发器所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。