MySQL触发器

业务需要，要同步两个数据库的用户信息，因此用到了触发器。

触发器是纯数据库操作，与项目代码无关。

 

触发器是数据库表的属性,是一个特殊的存储过程，触发器是个特殊的存储过程，但是触发器不需要CALL语句调用，也不需要手动启动。它由事件触发，事件包括INSERT，UPDATE和DELETE语句，当表中出现这些特定事件时，将激活该对象 。MySQL5.0.2开始支持。

 

触发器和触发事件是一个原子操作，不用担心数据完整性。

无法在一个时间上创建两个触发器。

 

一、创建触发器

语法：

①：单SQL语句语法

CREATE TRIGGER synchronization_user_insert AFTER INSERT ON user_table 
FOR EACH ROW 
INSERT INTO b.user2(`id`,`name`) 
VALUES(NEW.id,NEW.name);

 

#什么时候触发after或者before；操作事件（触发器类型）包括 insert,update,delete； ON 后面绑定表名

#for each row每一行受影响，影响范围

# b表示不同的数据库库名，同一个数据库可省略。可见触发器是一个全局函数

#NEW表示操作后新的值，OLD表示操作前的值

 

②：创建有多个执行语句的触发器: （MySQL默认以分号结尾）

DELIMITER //
CREATE TRIGGER synchronization_user_insert AFTER INSERT ON user_table 
FOR EACH ROW 
BEGIN
INSERT INTO b.user2(`id`,`name`) 
VALUES(NEW.id,NEW.name);
END //

DELIMITER ;

③：if语句触发器

DELIMITER //
CREATE TRIGGER 触发器名称 AFTER INSERT ON 表名 
FOR EACH ROW 
if 判断条件   THEN

执行SQL语句1；
ELSE 执行SQL语句2；
END if；

//

DELIMITER ;

 

④：case语句

DELIMITER //
CREATE TRIGGER 触发器名称 AFTER INSERT ON 表名 
FOR EACH ROW 
CASE

WHEN  执行SQL语句1；
WHEN  执行SQL语句2；
END CASE；

//

DELIMITER ;

 

二、查看触发器

　　SHOW TRIGGERS;

 

三、删除触发器

　　DROP TRIGGER [数据库名称.]触发器名称;

 

四、实际使用记录

　　在单SQL语句时，是不用加begin和end的。第一次加了成功创建了触发器，但是在业务执行时出错。显示找不到B数据库的B表

　　刚开始以为是JDBC数据源连接的问题因为是报了这个错，但是触发器是纯数据库的操作，跟这些无关。又考虑到账号权限的问题，但是root。最后在log日志发现下面的问题，所以看日志还是要看下面具体问题的原因分析。

　　Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`qibao_business`.`userdetail`, CONSTRAINT `fk_UserDetail_suyidept` FOREIGN KEY (`reptId`) REFERENCES `suyidept` (`id`))

　　很明显这是一个主外键关联的错误！

 

引用大佬的话：

！！尽量少使用触发器。

　　假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高，资源消耗要小。

　　触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。 

为什么大家都不推荐使用MySQL触发器而用存储过程？- segmentfault
回答1：
1.存储过程和触发器二者是有很大的联系的，我的一般理解就是触发器是一个隐藏的存储过程，因为它不需要参数，不需要显示调用，往往在你不知情的情况下已经做了很多操作。从这个角度来说，由于是隐藏的，无形中增加了系统的复杂性，非DBA人员理解起来数据库就会有困难，因为它不执行根本感觉不到它的存在。
2.再有，涉及到复杂的逻辑的时候，触发器的嵌套是避免不了的，如果再涉及几个存储过程，再加上事务等等，很容易出现死锁现象，再调试的时候也会经常性的从一个触发器转到另外一个，级联关系的不断追溯，很容易使人头大。其实，从性能上，触发器并没有提升多少性能，只是从代码上来说，可能在coding的时候很容易实现业务，所以我的观点是：摒弃触发器！触发器的功能基本都可以用存储过程来实现。
3.在编码中存储过程显示调用很容易阅读代码，触发器隐式调用容易被忽略。
4.存储过程的致命伤在于移植性，存储过程不能跨库移植，比如事先是在mysql数据库的存储过程，考虑性能要移植到oracle上面那么所有的存储过程都需要被重写一遍。

转载于:https://www.cnblogs.com/zeussbook/p/10593662.html