MYSQL常见概念二 : 触发器

 定义：  数据库某张表的增加、删除、更新的操作触发预先定义的sql执行

 特性：  引起触发器执行的操作与触发器内定义的sql，要么一起执行，要么一起不执行

 应用场景：  1.   数据备份，往一张表插入数据，需要往另外一张表同步时

                    2.   同步更新，更新一张表数据时，同时更新多张表相应数据，比如更改了用户名，在所有有改用户名的表都做

                                          相应更新

                    3.  想干以上两件事，又不想动线上代码

 缺点：  容易与代码逻辑重叠，开发人员维护困难；单纯使用触发器，可能会引起数据不一致(出错无法回滚)，可与事务结合使用

 创建触发器语法： 

CREATE TRIGGER trigger_name   -- 要创建的触发名称
trigger_time                  -- 触发器执行时间，值为 BEFORE 或 AFTER
trigger_event                 -- 触发事件，值为 INSERT、UPDATE 或 DELETE 
ON tbl_name                   -- 在哪个表上建立触发器
FOR EACH ROW                  -- 对更改/插入/删除的每一行执行
trigger_stmt                  -- 触发器sql，一句SQL语句或者用 BEGIN 和 END包含的多条语句

实例：

首先创建两张表：

DROP TABLE IF EXISTS users ;
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pass` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '2018-01-01 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


DROP TABLE IF EXISTS institution_users;
CREATE TABLE `institution_users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `institution_id` int(11) unsigned,
  `user_id` int(11) unsigned,
  `user_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '2018-01-01 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 1.1 建立 insert触发器，往users表插入数据时同时触发往institution_users插入数据

DROP TRIGGER IF EXISTS  insert_users_to_institution_users;   -- 如果有触发器先删除
CREATE TRIGGER insert_users_to_institution_users   -- 要创建的触发名称
AFTER                                              -- 触发器执行时间，值为 BEFORE 或 AFTER
INSERT                                             -- 触发事件，值为 INSERT、UPDATE 或 DELETE 
ON users                                            -- 在哪个表上建立触发器
FOR EACH ROW                                       -- 对更改/插入/删除的每一行执行
BEGIN                                              -- 触发器sql，一句SQL语句或者用 BEGIN 和 END包含的多条语句
    INSERT INTO institution_users(institution_id,user_id,user_name,created_at) VALUES(170,new.id,new.name,NOW());
END

1.2 执行insert ，往users表插入一条数据

INSERT INTO users(name,pass,created_at) VALUES('test_trigger','123456',NOW());

    执行成功后，查看 institution_users表，可以发现已经多了条记录

2.1 建立 update触发器，往users表更新数据时同时触发往institution_users更新数据

DROP TRIGGER IF EXISTS  update_users_to_institution_users;   -- 如果有触发器先删除
CREATE TRIGGER update_users_to_institution_users   -- 要创建的触发名称
AFTER                                              -- 触发器执行时间，值为 BEFORE 或 AFTER
UPDATE                                             -- 触发事件，值为 INSERT、UPDATE 或 DELETE 
ON users                                            -- 在哪个表上建立触发器
FOR EACH ROW                                       -- 对更改/插入/删除的每一行执行
BEGIN                                              -- 触发器sql，一句SQL语句或者用 BEGIN 和 END包含的多条语句
    update institution_users set user_name = new.name WHERE user_id = new.id;
END

2.2 执行成功后执行以下sql语句，会发现institution_users也有对应更新

UPDATE users SET name = 'dev_trigger'  WHERE name = 'test_trigger' ;

3.1 建立 delete触发器  ，删除users表数据时同时触发删除institution_users数据

DROP TRIGGER IF EXISTS  delete_users_to_institution_users;   -- 如果有触发器先删除
CREATE TRIGGER delete_users_to_institution_users   -- 要创建的触发名称
AFTER                                              -- 触发器执行时间，值为 BEFORE 或 AFTER
DELETE                                            -- 触发事件，值为 INSERT、UPDATE 或 DELETE 
ON users                                            -- 在哪个表上建立触发器
FOR EACH ROW                                       -- 对更改/插入/删除的每一行执行
BEGIN                                              -- 触发器sql，一句SQL语句或者用 BEGIN 和 END包含的多条语句
    DELETE from institution_users  WHERE user_id = old.id;
END

3.2 执行成功后执行以下sql语句，会发现institution_users也有对应删除

DELETE FROM users WHERE name = 'dev_trigger'