mysql 基础-触发器

触发器就像是一个被动技能，满足条件执行。

为什么使用触发器

简单的说，就是一张表发生了某件事（插入、删除、更新操作），然后自动触发了预先编写好的若干条SQL语句的执行；

触发器优点

特点：触发事件的操作和触发器里的SQL语句是一个事务操作，具有原子性,要么全部执行，要么都不执行；

作用：保证数据的完整性，起到约束的作用；

为什么慎用触发器

触发器是隐藏到mysql后台运行的功能。

1. 由于触发器是隐藏的，所以在别人使用某个表的时候而不知道已经创建了触发器（这个是经常发生的）的时候可能会创建一个同类的触发器或者是在自己的代码中加入了你创建触发器的类似逻辑，这样就会导致最后出的数据混乱。
2. 数据不易排查问题，后端写代码的时候由于没有把处理的部分逻辑加到代码中，最终会导致debug的时候不易排查问题。
3. 复杂逻辑，难免会出现触发器的，如果这个时候在加上几个存储过程，再加上事务等，最终可能会导致死锁。
4. 数据迁移时不方便（听淘宝的一个dba说的，我没有做过有触发器的数据迁移）。会导致后端和测试花大量的时间去检验数据。
5. 上述缺点太多，劣势大于优势。

触发器的四要素

        1.监视地点(table)
        2.监视事件(insert/update/delete) 
        3.触发时间(after/before)  
        4.触发事件(insert/update/delete)

触发频率：针对每一行执行

触发器语法

create trigger 触发器名称 trigger_time trigger_event
on 表名 for each row begin
操作
end
;;
DELIMITER ;

注意

触发器只能建立在永久表上，不能对临时表进行触发。

触发器只能对同一个表相同触发时间的相同触发事件，只能定义一个触发器。如果两个同类的触发器，那么只会触发最后创建的触发器。（但是不会报错的哟）

关键字说明

• DELIMITER：改变输入的结束符，默认情况下输入结束符是分号；，这里把它改成了两个分号 ;;，这样做的目的是把多条含分号的语句做个封装，全部输入完之后一起执行，而不是一遇到默认的分号结束符就自动执行；（在高版本的mysql中如果在在语句中没有出现";",是可以不适用这个的）
• for each row：  其中的 for each row 表示是行级触发的，也就是以行为单位。可能有人会问：难道还可以有其他的单位吗？答案是有。也有数据库支持语句级触发。但是目前对于 MySQL 来说 for each row 几乎是必须只能这么写
• new：当触发插入和更新事件时可用，指向是被操的作的记录
• old： 当触发删除和更新事件时可用，指向的是被操作的记录

查看触发器

和查看数据库（show databases;）查看表格（show tables;）一样，查看触发器的语法如下：

SHOW TRIGGERS [FROM schema_name]

其中，schema_name 即 Schema 的名称，在 MySQL 中 Schema 和 Database 是一样的，也就是说，可以指定数据库名，这样就不必先“USE database_name;”了。

删除触发器

和删除数据库、删除表格一样，删除触发器的语法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

触发器的执行顺序  

我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：

1. 如果 BEFORE 触发器执行失败，SQL 无法正确执行。
2. SQL 执行失败时，AFTER 型触发器不会触发。
3. AFTER 类型的触发器执行失败，SQL 会回滚

示例

创建表 用户表和用户历史表（用于测试）

• 1.创建对应表

  DROP TABLE IF EXISTS `user`;
  CREATE TABLE `user` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `account` varchar(255) DEFAULT NULL,
    `name` varchar(255) DEFAULT NULL,
    `address` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  DROP TABLE IF EXISTS `user_history`;
  CREATE TABLE `user_history` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) NOT NULL,
    `operatetype` varchar(200) NOT NULL,
    `operatetime` datetime NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 2.创建两个同表但是不同触发类型的触发器

  DROP TRIGGER IF EXISTS `tri_insert_user_alter`;
  DELIMITER ;;
  CREATE TRIGGER `tri_insert_user_alter` AFTER INSERT ON `user` FOR EACH ROW begin
      INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (new.id, 'add a user',  now());
  end
  ;;
  DELIMITER ;

  
  DROP TRIGGER IF EXISTS `tri_insert_user_alter`;
  DELIMITER ;;
  CREATE TRIGGER `tri_insert_user_alter` AFTER INSERT ON `user` FOR EACH ROW begin
      INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (new.id, 'add a user2',  now());
  end
  ;;
  DELIMITER ;