MySql触发器使用

2016-10-10 本文已影响2149人爱折腾的傻小子

MySql触发器参看文档
触发器：在指定表上，(insert(插入)、update(跟新)、delete(删除))事件动作,触发（After(之后)时机,Before(之前)），执行指定的一群或一个sql语句。
(举例解释：就是在delete删除之前(before)或之后(after)执行指定一个或一群sql语句)
MySQL支持触发器：在Mysql 5.0版本以上。在doc命令查看mysql版本命令(mysql --version)

关于触发器在Navicat for 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包含的索条语句

由上面，可以知道MYSQL可以创建6种类型的触发器。
（BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE）
（AFTER INSERT、AFTER UODATE、AFTER DELETE）
并且一张表上不能创建两个相同类型的触发器，因此一张表上面最多能创建6种类型的触发器。

trigger_event详解

INSERT型触发器：插入某一行时激活触发器，可能INSERT、LOAD DATA、REPLACE语句触发。
UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发。
DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。

LOAD DATA：语句用于将一个文件装入到一个数据表中，相当于一系列的INSERT操作。参考文档
REPLACE：语句和INSERT语句很像，只是在表中有primary key或unique索引时，如果插入的数据和原来的primary key或unique索引一致时，会先删除原来的数据，然后增加一条新的数据，就是REPLACE语句有时候等价一条INSERT语句，有时候等价一条DELETE语句加上一条INSERT语句。

BEGIN...END详解
在Mysql中,BEGIN...END语句的语法为：

BEGIN
[statement_list]
END

statement_list：使用方法
statement_list 代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾(默认值)
我们可以使用DELIMITERE指定自定义的定界符。
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$：DELIMITER $(注意：我们修改了定界符使用完了，记得修改回来DELIMITER ;)
参考代码：

Mysql中使用DECLARE来定义一句局部变量，该变量只能在BEGIN...END复合语句中使用，并且应该定义在复合语句的开头。
DECLARE var_name[,...] type [DEFAULT value]
var_name：变量名称，同 SQL 语句一样，变量名不区分大小写
type：数据类型MySQL 支持的任何数据类型,可以同时定义多个同类型的变量，用逗号隔开
DEFAULT:变量初始值为 NULL，如果需要，可以使用 DEFAULT 子句提供默认值，值可以被指定为一个表达式
对变量赋值采用SET语句：
SET var_name = expr [,var_name = expr] ...
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表示的是表名称。

删除触发器
和删除数据表、删除表格一样，语法如下：
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

触发器的执行顺序
触发器建立的一般是InnoDB数据库，上面要使用的表也是要事务支持的。
1.如果BEFORE触发器执行失败，SQL无法正确执行。
2.SQL执行失败时，AFTER型触发器不会触发。
3.AFTER类型的触发器执行失败，SQL会回滚。

Mysql触发器的使用举例：

Mysql触发器案例：

CREATE TRIGGER test_tt AFTER DELETE ON `test`  FOR EACH ROW
BEGIN
DECLARE s VARCHAR(20) DEFAULT 'hello';
SET s = 'world';
UPDATE `member` SET `name` = s WHERE id = OLD.id;
END

当在删除test表的数据时，会在对应的member对应的id中添加name字段值为world。

如果使用下面这种sql语句，一值得到null。

CREATE TRIGGER test_tt AFTER DELETE ON `test`  FOR EACH ROW
BEGIN
DECLARE s VARCHAR(20) DEFAULT 'hello';
SET s= (select name from test where id = old.id);
SET s = concat('a','hello');
UPDATE `member` SET `name` = s WHERE id = OLD.id;
END

这是由于select中old是在数据被删除了，再去查找，就会一直找不到。

参看下面案例：

CREATE TRIGGER test_tt AFTER DELETE ON `test`  FOR EACH ROW
BEGIN
UPDATE `member` SET `name` = old.name WHERE id = OLD.id;
END

在删除test表中的一行数据时，能够把删除之前的name数据写入member表中去。
可以见得： old表示的是之前的一整行完整的数据，并且是通过 . 的形式访问字段内容。

如果以上代码存在问题，请指正。谢谢。

MySql触发器使用

trigger_event详解

BEGIN...END详解

查看触发器

删除触发器

触发器的执行顺序

Mysql触发器的使用举例：

猜你喜欢

热点阅读