MySQL触发器使用详解
MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
创建触发器
在MySQL中，创建触发器语法如下：
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。
另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。
trigger_event 详解
MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外，还定义了 LOAD DATA 和 REPLACE 语句，这两种语句也能引起上述6中类型的触发器的触发。
LOAD DATA 语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT 操作。
REPLACE 语句一般来说和 INSERT 语句很像，只是在表中有 primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句有时候等价于一条。
INSERT 语句，有时候等价于一条 DELETE 语句加上一条 INSERT 语句。
INSERT 型触发器：插入某一行时激活触发器，可能通过 INSERT、LOAD DATA、REPLACE 语句触发；
UPDATE 型触发器：更改某一行时激活触发器，可能通过 UPDATE 语句触发；
DELETE 型触发器：删除某一行时激活触发器，可能通过 DELETE、REPLACE 语句触发。
BEGIN … END 详解
在MySQL中，BEGIN … END 语句的语法为：
BEGIN
[statement_list]
END
其中，statement_list 代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾。
而在MySQL中，分号是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，解释器遇到statement_list 中的分号后就开始执行，然后会报出错误，因为没有找到和 BEGIN 匹配的 END。
这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一条命令，不需要语句结束标识，语法为：
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$：
DELIMITER $
在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。
一个完整的创建触发器示例
假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：
DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;
变量详解
MySQL 中使用 DECLARE 来定义一局部变量，该变量只能在 BEGIN … END 复合语句中使用，并且应该定义在复合语句的开头，
即其它语句之前，语法如下：
DECLARE var_name[,...] type [DEFAULT value]
其中：
var_name 为变量名称，同 SQL 语句一样，变量名不区分大小写；type 为 MySQL 支持的任何数据类型；可以同时定义多个同类型的变量，用逗号隔开；变量初始值为 NULL，如果需要，可以使用 DEFAULT 子句提供默认值，值可以被指定为一个表达式。
对变量赋值采用 SET 语句，语法为：
SET var_name = expr [,var_name = expr] ...
NEW 与 OLD 详解
上述示例中使用了NEW关键字，和 MS SQL Server 中的 INSERTED 和 DELETED 类似，MySQL 中定义了 NEW 和 OLD，用来表示
触发器的所在表中，触发了触发器的那一行数据。
具体地：
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）
另外，OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。
查看触发器
和查看数据库（show databases;）查看表格（show tables;）一样，查看触发器的语法如下：
SHOW TRIGGERS [FROM schema_name];
其中，schema_name 即 Schema 的名称，在 MySQL 中 Schema 和 Database 是一样的，也就是说，可以指定数据库名，这样就
不必先“USE database_name;”了。
删除触发器
和删除数据库、删除表格一样，删除触发器的语法如下：
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
触发器的执行顺序
我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：
①如果 BEFORE 触发器执行失败，SQL 无法正确执行。
②SQL 执行失败时，AFTER 型触发器不会触发。
③AFTER 类型的触发器执行失败，SQL 会回滚。
小伙伴们是否对mysql的触发器的使用有所了解了呢，如有疑问，就给我留言吧，大家共同进步。
相关推荐
-
MySQL 触发器详解及简单实例
MySQL 触发器简单实例 语法 CREATE TRIGGER <触发器名称> --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象. { BEFORE | AFTER } --触发器有执行的时间设置:可以设置为事件发生前或后. { INSERT | UPDATE | DELETE } --同样也能设定触发的事件:它们可以在执行insert.update或delete的过程中触发. ON <表名称> --触发器是属于某一个表
-
Mysql中的触发器简单介绍及使用案例
什么是触发器? 触发器是数据库的一个程序,他是用来监听着数据表的某个行为,一旦数据表的这个行为发生了,马上执行相应的sql语句 触发器的语法结构: create trigger 触发器的名称触发器事件 on 监听的表名 for each row 行为发生后执行的sql语句 触发器事件组成::两部分组成: 触发器事件发生的时间-----是在监听的表的行为 after before 常用的是after 触发器执行的内容:增删改 创建order 表的时候,需要注意,因为order在mysql中是一个关
-
mysql 触发器用法实例详解
MySQL触发器语法详解: 触发器 trigger是一种特殊的存储过程,他在插入(inset).删除(delete)或修改(update)特定表中的数据时触发执行,它比数据本身标准的功能更精细和更复杂的数据控制能力.触发器不是由程序调用,而是由某个事件来触发的.在有数据修改时自动强制执行其业务规则,经常用于加强数据的完整性约束和业务规则等.触发器可以查询其他表,而且包含复制的sql语句.触发器也可用于强制引用完整性.触发器可以强制比用check约束定义的约束更为复杂的约束. (一).CREAT
-
MySQL存储过程中的基本函数和触发器的相关学习教程
MySQL存储过程的常用函数 一.字符串类 CHARSET(str) //返回字串字符集 CONCAT (string2 [,... ]) //连接字串 INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0 LCASE (string2 ) //转换成小写 LEFT (string2 ,length ) //从string2中的左边起取length个字符 LENGTH (string ) //string长度 LOAD_FI
-
MySQL触发器 Update触发Insert失败
今天工作需要,想要实现将仅对状态更新的表进行历史记录显示,于是考虑在原表中建立触发器,将更新的内容同时写入另一张表 于是进行测试 --建立测试表CREATE TABLE `triggletest_triggle` ( `id` INT(11) NOT NULL, `name` VARCHAR(5) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB --建立目标表 CREATE TAB
-
MySQL与Oracle 差异比较之六触发器
触发器 编号 类别 ORACLE MYSQL 注释 1 创建触发器语句不同 create or replace trigger TG_ES_FAC_UNIT before insert or update or delete on ES_FAC_UNIT for each row create trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_unit` for each row
-
MySQL触发器使用详解
MySQL包含对触发器的支持.触发器是一种与表操作有关的数据库对象,当触发器所在表上出现指定事件时,将调用该对象,即表的操作事件触发表上的触发器的执行. 创建触发器 在MySQL中,创建触发器语法如下: 复制代码 代码如下: CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt 其中: trigger_name:标识触发器名称,用户自行指定: trigger_tim
-
Mysql事项,视图,函数,触发器命令(详解)
事项开启和使用 //修改表的引擎 alter table a engine=myisam; //开启事务 begin; //关闭自动提交 set autocommit=0; //扣100 update bank set money=money-100 where bid=1; //回滚,begin开始的所有sql语句操作 rollback; //开启事务 begin; //关闭自动提交 set autocommit=0; //扣100 update bank set money=money-10
-
MySQL高级学习笔记（三）：Mysql逻辑架构介绍、mysql存储引擎详解
Mysql逻辑架构介绍总体概览 和其它数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用.主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离 . 这种架构可以根据业务的需求和实际需要选择合适的存储引擎. controller层: Connectors:连接层,c .java等连接mysql 业务逻辑处理成: Connection Pool:连接层 c3p0连接池等 Manager Service util:备份.容灾
-
MySQL Truncate用法详解
前言: 当我们想要清空某张表时,往往会使用truncate语句.大多时候我们只关心能否满足需求,而不去想这类语句的使用场景及注意事项.本篇文章主要介绍truncate语句的使用方法及注意事项. 1.truncate使用语法 truncate的作用是清空表或者说是截断表,只能作用于表.truncate的语法很简单,后面直接跟表名即可,例如:truncate table tbl_name 或者 truncate tbl_name . 执行truncate语句需要拥有表的drop权限,从逻辑上讲,tr
-
MySQL 权限控制详解
mysql权限控制 作为一名DBA,想必大家对MySQL中的权限都不陌生,MySQL中对于权限的控制分为三个层面: 全局性的管理权限,作用于整个MySQL实例级别 数据库级别的权限,作用于某个指定的数据库上或者所有的数据库上 数据库对象级别的权限,作用于指定的数据库对象上(表.视图等)或 者所有的数据库对象上 这里,我们将mysql中的所有权限列出来,最后给出一个特殊的案例来反应mysql权限控制中的一个小bug.首先来看权限列表,权限的顺序按照首字母的顺序进行排列: •All/All Priv
-
MySQL数据库约束详解
目录 基本介绍 1.primary key(主键) 细节说明 2.not null(非空) 3.unique(唯一) unique细节 4.foreign key(外键) foreign key(外键) 语法 外键细节说明 5.check check 6.练习 7.自增长 基本介绍 约束用于确保数据库的数据满足特定的商业规则 在mysql中,约束包括:not null,unique,primary key,foreign key 和check 5种 1.primary key(主键) 字段名 字
-
Oracle10个分区和Mysql分区区别详解
Oracle10g分区常用的是:range(范围分区).list(列表分区).hash(哈希分区).range-hash(范围-哈希分区).range-list(列表-复合分区). Range分区:Range分区是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中. 如按照时间划分,2010年1月的数据放到a分区,2月的数据放到b分区,在创建的时候,需要指定基于的列,以及分区的范围值. 在按时间分区时,如果某些记录暂无法预测范围,可以创建m
-
MySQL 序列 AUTO_INCREMENT详解及实例代码
MySQL 序列 AUTO_INCREMENT详解及实例代码 MySQL序列是一组整数:1, 2, 3, ...,由于一张数据表只能有一个字段自增主键, 如果你想实现其他字段也实现自动增加,就可以使用MySQL序列来实现. 本章我们将介绍如何使用MySQL的序列. 使用AUTO_INCREMENT MySQL中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列. 实例 以下实例中创建了数据表insect, insect中id无需指定值可实现自动增长. mysql>
-
MySQL prepare原理详解
Prepare的好处 Prepare SQL产生的原因.首先从mysql服务器执行sql的过程开始讲起,SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行.词法分析->语法分析这两个阶段我们称之为硬解析.词法分析识别sql中每个词,语法分析解析SQL语句是否符合sql语法,并得到一棵语法树(Lex).对于只是参数不同,其他均相同的sql,它们执行时间不同但硬解析的时间是相同的.而同一SQL随着查询数据的变化,多次查询执行时间可能不同,但硬解
-
CentOS 7.0下使用yum安装mysql的方法详解
CentOS7默认数据库是mariadb,配置等用着不习惯,因此决定改成mysql,但是CentOS7的yum源中默认好像是没有mysql的.为了解决这个问题,我们要先下载mysql的repo源. 1.下载mysql的repo源 $ wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm 2.安装mysql-community-release-el7-5.noarch.rpm包 $ sudo rpm -ivh mys