【详细】MySQL之触发器TRIGGER

一、概述

触发器是与表有关的数据库对象，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发触发器中定义的语句并执行。
另外，注意触发器是不能定义在临时表或视图上

二、作用

记录日志：比如删除用户表数据时，我们有时候需要记录日志，一般会通过代码实现，其实我们也可以通过触发器来当有删除操作时自动记录日志到日志表中
再比如在下订单的时候，对应的商品的库存量要相应的减少，可以通过触发器去减少库存表中对应库存值 …

任何一条记录上的操作满足触发事件都会触发该触发器，这就大大降低了增删改的效率(例如，定义一个触发器触发时机是当有数据插入时，假如插入100条数据，那么触发器也会触发100次)，因此，在日常业务中应尽量避免使用触发器

三、创建

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：表名
-- FOR EACH ROW：必不可少,mysql只有行级触发器
-- trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

1、创建只有一个执行语句的触发器
见示例七
2、创建有多个执行语句的触发器

mysql> delimiter $$
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    -> 　　IF NEW.amount < 0 THEN
    -> 　　　　SET NEW.amount = 0;
    -> 　　ELSEIF NEW.amount > 100 THEN
    -> 　　　　SET NEW.amount = 100;
    -> 　　END IF;
    -> END$$
mysql> delimiter ;

四、查看

show triggers

五、删除

DROP TRIGGER 触发器名

六、NEW与OLD详解

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

① 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
② 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
③ 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；
使用方法：NEW.columnName （columnName为相应数据表某一列名）
另外，OLD是只读的，而NEW则可以在触发器中使用 SET 赋值

七、应用示例

1、创建userinfo表存储员工基本信息

-- ----------------------------
-- Table structure for userinfo
-- ----------------------------
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo`  (
  `id` int(11) NOT NULL,
  `userName` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `passWord` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `sex` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `phone` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES (1, '李虎', '123456', 25, '男', '17694855647');
INSERT INTO `userinfo` VALUES (2, '吴霞', '456789', 20, '女', '13894567899');
INSERT INTO `userinfo` VALUES (3, '张杰', '666666', 16, '男', '15687486985');
INSERT INTO `userinfo` VALUES (23, '赵雷', '7788', 32, '男', '14525896544');

2、创建员工工资表

-- ----------------------------
-- Table structure for job
-- ----------------------------
DROP TABLE IF EXISTS `job`;
CREATE TABLE `job`  (
  `id` int(20) NOT NULL,
  `job` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `salary` int(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `INDEX_JOB`(`job`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of job
-- ----------------------------
INSERT INTO `job` VALUES (1, 'java初级开发工程师', 6000);
INSERT INTO `job` VALUES (2, 'c++中级开发工程师', 16000);
INSERT INTO `job` VALUES (3, '数据库工程师', 10000);
INSERT INTO `job` VALUES (4, 'java高级工程师', 25000);
INSERT INTO `job` VALUES (23, 'HR', 4000);

3、在userinfo表上创建触发器
删除userinfo表数据时同时删除工资表相关信息

DROP TRIGGER IF EXISTS `delJobTrigger`;
delimiter ;;
CREATE TRIGGER `delJobTrigger` AFTER DELETE ON `userinfo` FOR EACH ROW DELETE FROM job WHERE id=OLD.id;;

4、执行删除操作触发触发器

DELETE FROM userinfo WHERE id = 23

执行完成后再去查询job表中数据，发现id = 23的数据也被删除了