mysql触发器trigger实例详解

吾爱主题阅读：186 2024-04-02 08:00:38 评论：0

mysql好像从5.0.2版本就开始支持触发器的功能了，本次博客就来介绍一下触发器，首先还是谈下概念性的东西吧：

什么是触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

创建触发器

创建触发器的语法如下：

1 2 3 4 5 6 7 8 9 create trigger trigger_name trigger_time trigger_event on tb_name for each row trigger_stmt trigger_name：触发器的名称 tirgger_time：触发时机，为before或者 after trigger_event：触发事件，为 insert 、 delete 或者 update tb_name：表示建立触发器的表明，就是在哪张表上建立触发器 trigger_stmt：触发器的程序体，可以是一条sql语句或者是用 begin 和 end 包含的多条语句 所以可以说mysql创建以下六种触发器： before insert ,before delete ,before update after insert , after delete , after update

其中，触发器名参数指要创建的触发器的名字

before和after参数指定了触发执行的时间，在事件之前或是之后

for each row表示任何一条记录上的操作满足触发事件都会触发该触发器

创建有多个执行语句的触发器

1 2 3 4 5 create trigger 触发器名 before| after 触发事件 on 表名 for each row begin 执行语句列表 end

其中，begin与end之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开

tips：一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突

　　为解决此问题可用delimiter，如：delimiter ||，可以将结束符号变成||

　　当触发器创建完成后，可以用delimiter ;来将结束符号变成;

1 2 3 4 5 6 7 8 9 10 11 mysql> delimiter || mysql> create trigger demo before delete -> on users for each row -> begin -> insert into logs values (now()); -> insert into logs values (now()); -> end -> || query ok, 0 rows affected (0.06 sec) mysql> delimiter ;

上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作

就会执行begin和end中的语句，接着使用||结束

最后使用delimiter ; 将结束符号还原

tigger_event：

load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的情况下和insert差不多，但是如果表中存在primary 或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。

触发器可以是一条sql语句，也可以是多条sql代码块，那如何创建呢？

1 2 3 4 5 6 7 8 delimiter $ #将语句的分隔符改为$ begin sql1; sql2; ... sqln end $ delimiter ; #将语句的分隔符改回原来的分号 ";"

在begin...end语句中也可以定义变量，但是只能在begin...end内部使用：

declare var_name var_type [default value] #定义变量，可指定默认值
set var_name = value #给变量赋值

new和old的使用:

根据以上的表格，可以使用一下格式来使用相应的数据：

new.columnname：新增行的某列数据
old.columnname：删除行的某列数据

说了这么多现在我们来创建一个触发器吧！

现在有表如下：

用户users表

1 2 3 4 5 6 7 create table `users` ( `id` int (11) unsigned not null auto_increment, ` name ` varchar (255) character set utf8mb4 default null , `add_time` int (11) default null , primary key (`id`), key ` name ` (` name `(250)) using btree ) engine=myisam auto_increment=1000001 default charset=latin1;

日志logs表：

1 2 3 4 5 create table `logs` ( `id` int (11) not null auto_increment, `log` varchar (255) default null comment '日志说明' , primary key (`id`) ) engine=innodb default charset=utf8mb4 comment= '日志表' ;

需求是：当在users中插入一条数据，就会在logs中生成一条日志信息。

创建触发器：

1 2 3 4 5 6 7 8 9 10 delimiter $ create trigger user_log after insert on users for each row begin declare s1 varchar (40) character set utf8; declare s2 varchar (20) character set utf8;#后面发现中文字符编码出现乱码，这里设置字符集 set s2 = " is created" ; set s1 = concat(new. name ,s2); #函数concat可以将字符串连接 insert into logs(log) values (s1); end $ delimiter ;

这里我用的navicat:

查看触发器show triggers语句查看触发器信息

tip:

上面我用的navicat直接创建，如果大家用的mysql front，name这里会有个区别，我们删除刚才的触发器，在mysql front中测试

drop trigger user_log;#删除触发器

打开mysql front：

mysql front在编译sql时，不用定义结尾分隔符，修改后的sql直接这样既可：

1 2 3 4 5 6 7 8 9 10 #delimiter $ create trigger user_log after insert on users for each row begin declare s1 varchar (40) character set utf8; declare s2 varchar (20) character set utf8; set s2 = " is created" ; set s1 = concat(new. name ,s2); #函数concat可以将字符串连接 insert into logs(log) values (s1); end #$ #delimiter ;