创建触发器

• 触发器：相关表插入、更新或删除时激活。这些操作就是触发事件。触发器可以设置为在触发事件之前或之后激活。例如，可以设置一个触发器在每一行插入表之前激活，或者在每一行更新之后激活。 创建下面的表,然后添加几条记录：
  create table hotel ( id int primary key auto_increment, name varchar(10), level varchar(5), city varchar(10));
  insert into hotel ( name, level, city ) values ( "钓鱼台", "五星", "北京");
  insert into hotel ( name, level, city ) values ( "东方明珠", "四星", "上海");
  insert into hotel ( name, level, city ) values ( "南国风", "五星", "广州");
  insert into hotel ( name, level, city ) values ( "天府大酒店", "三星", "成都");

  
  mysql> select * from hotel;
  +----+------------+-------+------+
  | id | name       | level | city |
  +----+------------+-------+------+
  |  1 | 钓鱼台     | 五星  | 北京 |
  |  2 | 东方明珠   | 四星  | 上海 |
  |  3 | 南国风     | 五星  | 广州 |
  |  4 | 天府大酒店 | 三星  | 成都 |
  +----+------------+-------+------+
  4 rows in set (0.006 sec)
  
  mysql>
                                                 

  创建与表 hotel 列结构相同的表 hotel_backup ：
  create table hotel_backup ( id int primary key auto_increment, name varchar(10), level varchar(5), city varchar(10));



• 创建名为 trigger_delete 的触发器：
  

  
      delimiter //
      create trigger trigger_delete before delete on  hotel for each row
      begin
      insert into hotel_backup values( old.id, old.name, old.level, old.city);
      end
      //
                                                 

  
  mysql> delimiter //
  mysql>     create trigger trigger_delete before delete on  hotel for each row
      ->     begin
      ->     insert into hotel_backup values( old.id, old.name, old.level, old.city);
      ->     end
      ->     //
  Query OK, 0 rows affected (0.796 sec)
  
  mysql>
  
                                                 

  • delimiter // 将分隔符修改为 //
  
  
  • 创建完触发器后，执行命令： delimiter ;
    把分隔符由 // 恢复到原来的 ;



• 体验触发器的效果：
  delete from hotel where id =3;
  然后可以看到

  
    mysql>  select * from hotel;
  +----+------------+-------+------+
  | id | name       | level | city |
  +----+------------+-------+------+
  |  1 | 钓鱼台     | 五星  | 北京 |
  |  2 | 东方明珠   | 四星  | 上海 |
  |  4 | 天府大酒店 | 三星  | 成都 |
  +----+------------+-------+------+
  3 rows in set (0.006 sec)
  
  mysql>
                                                 

  
    mysql> select * from hotel_backup;
  +----+--------+-------+------+
  | id | name   | level | city |
  +----+--------+-------+------+
  |  3 | 南国风 | 五星  | 广州 |
  +----+--------+-------+------+
  1 row in set (0.006 sec)
  
  mysql>
                                                 

  • 可以看到，在表 hotel 删除一条记录前，会向表 hotel_backup 添加这条记录。
  
  

显示及删除触发器

• 显示已经创建的触发器:

  
      show triggers \G
                                  

  
    mysql> show triggers \G
  *************************** 1. row ***************************
               Trigger: trigger_delete
                 Event: DELETE
                 Table: hotel
             Statement: begin
      insert into hotel_backup values( old.id, old.name, old.level, old.city);
      end
                Timing: BEFORE
               Created: 2026-05-05 22:50:05.45
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
               Definer: root@%
  character_set_client: gbk
  collation_connection: gbk_chinese_ci
    Database Collation: utf8mb4_0900_ai_ci
  1 row in set (0.013 sec)
  
  mysql>
                                                 

  
  
  
• 删除触发器:

  
        drop trigger trigger_delete;