mysql触发器IF ELSE Mysql触发器类型

转载

mob64ca14150f43 2023-10-03 20:44:28

文章标签 mysql触发器IF ELSE 触发器 ci mysql 文章分类 MySQL 数据库

文章目录

1. 概念
2. 类型
3. 使用

3.1 insert触发器
3.2 update触发器
3.3 delete触发器
3.4 删除触发器
3.5 查看触发器

4. 总结

1. 概念

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。

但是需要注意以下几点：

触发器只能创建在永久表上
对于同一个表相同出发时间的相同触发条件，只能定义一个触发器
Mysql目前只支持行级触发
触发器按照before触发器、行操作、after触发器的顺序执行，其中任何一步发生错误都不会继续执行剩下的操作

如果针对于事务表进行的操作，整个事务就执行回滚操作
如果针对于非事务表，已经更新的记录将无法回滚

使用别名old和new来引用触发器中发生变化的记录内容

2. 类型

MySQL中目前支持如下的三类触发器：

类型	说明
insert触发器	new表示将要或者已经新增的数据
update触发器	old表示修改之前的数据，new表示将要或已经修改后的数据
delete触发器	old表示将要或者已经删除的数据

3. 使用

3.1 insert触发器

创建触发器的语法如下：

create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] -- 行级触发器
begin
	trigger_stmt ;
end;

这里继续使用之前的account表，表中记录如下：

mysql> select * from account;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | Forlogen |  1000 |
|  2 | Kobe     |   500 |
|  3 | James    |   800 |
+----+----------+-------+
3 rows in set (0.02 sec)

首先创建所需的日志表：

CREATE TABLE account_logs (
	id INT ( 11 ) NOT NULL auto_increment,
	operation VARCHAR ( 20 ) NOT NULL COMMENT '操作类型, insert/update/delete',
	operate_time datetime NOT NULL COMMENT '操作时间',
	operate_id INT ( 11 ) NOT NULL COMMENT '操作表的ID',
	operate_params VARCHAR ( 500 ) COMMENT '操作参数',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT charset = utf8;

接着创建一个insert触发器，完成插入数据时的日志记录：

delimiter $

create trigger account_insert_trigger
after insert
on account
for each row
begin
  insert into account_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'insert',now(),new.id,concat('after insert(id:',new.id,', name:',new.name,',money:',new.money,')'));
end $

delimiter ;

插入一条语句后查看account_logs表中信息：

mysql> select * from account_logs \G;
*************************** 1. row ***************************
            id: 1
     operation: insert
  operate_time: 2020-08-24 10:50:32
    operate_id: 4
operate_params: after insert(id:4, name:Ball,money:200)
1 row in set (0.00 sec)

3.2 update触发器

接着创建一个update触发器，如下所示：

delimiter $

create trigger account_update_trigger
after update
on account
for each row
begin
  insert into account_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'insert',now(),new.id,concat('before insert(id:',old.id,', name:',old.name,',money:',old.money,') after insert(id:',new.id,', name:',new.name,',money:',new.money,')'));
end $

delimiter ;

更新account表中一条记录，再去查看account_logs表，如下所示：

mysql> select * from account_logs \G;
*************************** 1. row ***************************
            id: 1
     operation: insert
  operate_time: 2020-08-24 10:50:32
    operate_id: 4
operate_params: after insert(id:4, name:Ball,money:200)
*************************** 2. row ***************************
            id: 2
     operation: insert
  operate_time: 2020-08-24 11:00:42
    operate_id: 4
operate_params: before insert(id:4, name:Ball,money:200) after insert(id:4, name:Ball,money:450)
2 rows in set (0.00 sec)

3.3 delete触发器

创建一个delete触发器，如下所示：

delimiter $

create trigger account_delete_trigger
after delete
on account
for each row
begin
  insert into account_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'delete',now(),old.id,concat('before delete(id:',old.id,', name:',old.name,',money:',old.money,')'));
end $

delimiter ;

删除之前插入的那一条记录，完了查看account_logs表中信息，如下所示：

mysql> select * from account_logs \G;
*************************** 1. row ***************************
            id: 1
     operation: insert
  operate_time: 2020-08-24 10:50:32
    operate_id: 4
operate_params: after insert(id:4, name:Ball,money:200)
*************************** 2. row ***************************
            id: 2
     operation: insert
  operate_time: 2020-08-24 11:00:42
    operate_id: 4
operate_params: before insert(id:4, name:Ball,money:200) after insert(id:4, name:Ball,money:450)
*************************** 3. row ***************************
            id: 3
     operation: delete
  operate_time: 2020-08-24 11:06:11
    operate_id: 4
operate_params: before delete(id:4, name:Ball,money:450)
3 rows in set (0.00 sec)

经过测试，上述的三类触发器都创建成功，并且可正常使用。

3.4 删除触发器

删除的语法如下：

drop trigger [schema_1 name.]trigger_name

3.5 查看触发器

使用show triggers \G;可以查看所有的触发器的状态和语法等信息，如下所示：

mysql> show triggers \G;
*************************** 1. row ***************************
             Trigger: account_insert_trigger
               Event: INSERT
               Table: account
           Statement: begin
  insert into account_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'insert',now(),new.id,concat('after insert(id:',new.id,', name:',new.name,',money:',new.money,')'));
end
              Timing: AFTER
             Created: 2020-08-24 10:50:08.22
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
             Trigger: account_update_trigger
               Event: UPDATE
               Table: account
           Statement: begin
  insert into account_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'insert',now(),new.id,concat('before insert(id:',old.id,', name:',old.name,',money:',old.money,') after insert(id:',new.id,', name:',new.name,',money:',new.money,')'));
end
              Timing: AFTER
             Created: 2020-08-24 11:00:11.52
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 3. row ***************************
             Trigger: account_delete_trigger
               Event: DELETE
               Table: account
           Statement: begin
  insert into account_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'delete',now(),old.id,concat('before delete(id:',old.id,', name:',old.name,',money:',old.money,')'));
end
              Timing: AFTER
             Created: 2020-08-24 11:05:58.23
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
3 rows in set (0.00 sec)

也可以使用desc information_schema.triggers;查看数据库中所有的触发器。如果想要查看指定名字的某一个触发器，可以使用select * from information_schema.triggers where trigger_name = 'trigger_name' \G;如下所示：

mysql> select * from information_schema.triggers where trigger_name = 'account_insert_trigger' \G ;
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: sql_store
              TRIGGER_NAME: account_insert_trigger
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: sql_store
        EVENT_OBJECT_TABLE: account
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: begin
  insert into account_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'insert',now(),new.id,concat('after insert(id:',new.id,', name:',new.name,',money:',new.money,')'));
end
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2020-08-24 10:50:08.22
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_0900_ai_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)