【详细】MySQL之触发器TRIGGER

1
2
3
4
5
6
7
8
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
3
4
5
6
7
8
9
10
11
12
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 ;

1
2
show triggers

1
2
DROP TRIGGER 触发器名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- ----------------------------
-- 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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- ----------------------------
-- 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);

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

1
2
DELETE FROM userinfo WHERE id = 23