MySQL触发器

2019-12-15 本文已影响0人丶Daniel

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 ;`)
参考代码：

image

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表示的是之前的一整行完整的数据，并且是通过 . 的形式访问字段内容。

------------------------------------------------------------------------------

触发器

触发器是（trigger)是个特殊的存储你过程，它的执行不是由程序调用，也不是手动启动，而是由事件来触发，比如当对一个表进行操作（INSERT,DELETE,UPDATE)时就会激活它执行。触发器经常用来加强数据的完整性约束和业务规则等。触发器可以从DBA_TRIGGERS, USER_TRIGGES数据字典中查到。
触发器语法

CREATE TRIGGER
trigger_name 
TRIGGER_TIME[AFTER,BEFORE] 
TRIGGER_EVENT[UPDATE,INSERT,DELETE]
ON tbl_name
FOR EACH ROW trigger_stmt

触发器实现两表同步

#同步插入
DELIMITER $ #修改定界符
DROP TRIGGER IF EXISTS sync_apps_insert$
CREATE TRIGGER sync_apps_insert AFTER INSERT ON rht_train.rht_apps FOR EACH ROW
BEGIN
INSERT INTO rht_idc.rhi_apps 
(id, appid, appname, isreward, adesc, adetail, price, downcount, sortid, iftj, flag, apptype, appcol, imgurl, ver, vernum, iconurl, filesize, lang, appurl, package, ctime, utime, stars, signature, unconfirm, develop, typeinfo) VALUES 
(NEW.id, NEW.appid, NEW.appname, NEW.isreward, NEW.adesc, NEW.adetail, NEW.price, NEW.downcount, NEW.sortid, NEW.iftj, NEW.flag, NEW.apptype, NEW.appcol, NEW.imgurl, NEW.ver, NEW.vernum, NEW.iconurl, NEW.filesize, NEW.lang, NEW.appurl, NEW.package, NEW.ctime, NEW.utime, NEW.stars, NEW.signature, NEW.unconfirm, NEW.develop, NEW.typeinfo);
END $
DELIMITER ; #还原定界符

#同步删除
DELIMITER $ #修改定界符
DROP TRIGGER IF EXISTS sync_apps_delete$
CREATE TRIGGER sync_apps_delete AFTER DELETE ON rht_train.rht_apps FOR EACH ROW
BEGIN
DELETE FROM rht_idc.rhi_apps WHERE rht_idc.rhi_apps.id=OLD.id;
END $
DELIMITER ; #还原定界符

#同步更新
DELIMITER $ #修改定界符
DROP TRIGGER IF EXISTS sync_apps_update$
CREATE TRIGGER sync_apps_update AFTER UPDATE ON rht_train.rht_apps FOR EACH ROW
BEGIN
UPDATE `rht_idc`.`rhi_apps` SET `id`=NEW.id, `appid`=NEW.appid, `appname`=NEW.appname, 
`isreward`=NEW.isreward, `adesc`=NEW.adesc, `adetail`=NEW.adetail, `price`=NEW.price, `downcount`=NEW.downcount, 
`sortid`=NEW.sortid, `iftj`=NEW.iftj, `flag`=NEW.flag, `apptype`=NEW.apptype, `appcol`=NEW.appcol, `imgurl`=NEW.imgurl, 
`ver`=NEW.ver, `vernum`=NEW.vernum, `iconurl`=NEW.iconurl, `filesize`=NEW.filesize, `lang`=NEW.lang, `appurl`=NEW.appurl, 
`package`=NEW.package, `ctime`=NEW.ctime, `utime`=NEW.utime, `stars`=NEW.stars, `signature`=NEW.signature,
`unconfirm`=NEW.unconfirm, `develop`=NEW.develop, `typeinfo`=NEW.typeinfo WHERE (rht_idc.rhi_apps.id=OLD.id);
END $
DELIMITER ; #还原定界符

如果执行返回

Trigger in wrong schema

请检查use datebases 是否是rht_train。触发器是建立在此库上的表，应当选择此库才能正常建立。

数据库

作者：益初
链接：https://www.jianshu.com/p/253f5a57ca37
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

MySQL触发器

trigger_event详解

BEGIN...END详解

查看触发器

删除触发器

触发器的执行顺序

Mysql触发器的使用举例：

------------------------------------------------------------------------------

触发器

触发器实现两表同步

猜你喜欢

热点阅读