gpt4 book ai didi

Mysql高级8-触发器

转载 作者:我是一只小鸟 更新时间:2023-08-17 06:31:06 26 4
gpt4 key购买 nike

1、触发器

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

使用别名old和new来引用触发器中发生变化的记录内容,这与其他的数据库是相似的,现在触发器还只支持行级触发,不支持语句级触发.

  。

2、触发器类型

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

  。

3、触发器语法

  3.1 创建语法

                          
                            create
                          
                          
                            trigger
                          
                          
                             trigger_name
before
                          
                          
                            /
                          
                          after 
                          
                            insert
                          
                          
                            /
                          
                          
                            update
                          
                          
                            /
                          
                          
                            delete
                          
                          
                            on
                          
                           tb_name 
                          
                            for
                          
                          
                             each row

                          
                          
                            begin
                          
                          
                            
  trigger_stmt;

                          
                          
                            end
                          
                          ;
                        

  3.2 查看语法

                          show triggers;
                        

  3.3 删除语句

                          
                            drop
                          
                          
                            trigger
                          
                          
                            [
                          
                          
                            schema_name.
                          
                          
                            ]
                          
                           trigger_name;  
                          
                            --
                          
                          
                             如果没有指定schema_name,默认为当前数据库
                          
                        

  。

4、插入数据触发器案例

  4.1 需求:

    通过触发器记录student表的数据插入时,将变更日志插入到日志表student_logs中; 。

  4.2 创建 student_logs表

                          mysql
                          
                            >
                          
                          
                            create
                          
                          
                            table
                          
                          
                             student_logs(
    
                          
                          
                            ->
                          
                           id 
                          
                            int
                          
                          
                            primary
                          
                          
                            key
                          
                          
                             auto_increment,
    
                          
                          
                            ->
                          
                           operation 
                          
                            varchar
                          
                          (
                          
                            20
                          
                          ) comment "操作类型,
                          
                            insert
                          
                          
                            /
                          
                          
                            update
                          
                          
                            /
                          
                          
                            delete
                          
                          
                            ",
    
                          
                          
                            ->
                          
                           operate_time 
                          
                            datetime
                          
                          
                             comment "操作时间",
    
                          
                          
                            ->
                          
                           operate_id 
                          
                            int
                          
                          
                             comment "操作的ID",
    
                          
                          
                            ->
                          
                           operate_params 
                          
                            varchar
                          
                          (
                          
                            500
                          
                          
                            ) comment "操作参数"
    
                          
                          
                            ->
                          
                          
                             )comment "student操作日志表";
Query OK, 
                          
                          
                            0
                          
                           rows affected (
                          
                            1.18
                          
                           sec)
                        

  4.3 创建插入数据触发器

                          mysql
                          
                            >
                          
                          delimiter 
                          
                            &
                          
                          
                            
mysql
                          
                          
                            >
                          
                          
                            create
                          
                          
                            trigger
                          
                          
                             student_insert_trigger
        
                          
                          
                            ->
                          
                           after 
                          
                            insert
                          
                          
                            on
                          
                           student 
                          
                            for
                          
                          
                             each row 
        
                          
                          
                            ->
                          
                          
                            begin
                          
                          
                            ->
                          
                          
                            insert
                          
                          
                            into
                          
                           student_logs(id,operation,operate_time,operate_id,operate_params) 
                          
                            values
                          
                           (
                          
                            null
                          
                          , 
                          
                            '
                          
                          
                            insert
                          
                          
                            '
                          
                          , now(), new.id, concat("插入的数据内容为:id
                          
                            =
                          
                          ",new.id,"name
                          
                            =
                          
                          ",new.name,"age
                          
                            =
                          
                          ",new.age,"score
                          
                            =
                          
                          
                            ",new.score));
        
                          
                          
                            ->
                          
                          
                            end
                          
                          
                            ;
        
                          
                          
                            ->&
                          
                          
                            
Query OK, 
                          
                          
                            0
                          
                           rows affected (
                          
                            0.20
                          
                           sec)   
                        

    说明1:delimiter &在之前的文章中已经提到过,是将mysql中的语句终止符由“;” 改为“&”,原因是,触发器中有完整的sql语句,会包含分号,如果不改,怎无法在终端中书写完整的触发器语句 。

  4.4 查看创建的触发器

                          mysql
                          
                            >
                          
                          
                             delimiter ;
mysql
                          
                          
                            >
                          
                          
                             show triggers;

                          
                          
                            +
                          
                          
                            --
                          
                          
                            ----------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+
                          
                          
                            |
                          
                          
                            Trigger
                          
                          
                            |
                          
                           Event  
                          
                            |
                          
                          
                            Table
                          
                          
                            |
                          
                           Statement                                                                                                                                                                                                                           
                          
                            |
                          
                           Timing 
                          
                            |
                          
                           Created                
                          
                            |
                          
                           sql_mode                                                                                                              
                          
                            |
                          
                           Definer                           
                          
                            |
                          
                           character_set_client 
                          
                            |
                          
                           collation_connection 
                          
                            |
                          
                          
                            Database
                          
                           Collation 
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            ----------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+
                          
                          
                            |
                          
                           student_insert_trigger 
                          
                            |
                          
                          
                            INSERT
                          
                          
                            |
                          
                           student 
                          
                            |
                          
                          
                            begin
                          
                          
                            insert
                          
                          
                            into
                          
                           student_logs(id,operation,operate_time,operate_id,operate_params) 
                          
                            values
                          
                           (
                          
                            null
                          
                          , 
                          
                            '
                          
                          
                            insert
                          
                          
                            '
                          
                          , now(), new.id, concat("插入的数据内容为:id
                          
                            =
                          
                          ",new.id,"name
                          
                            =
                          
                          ",new.name,"age
                          
                            =
                          
                          ",new.age,"score
                          
                            =
                          
                          ",new.score)); 
                          
                            end
                          
                          
                            |
                          
                           AFTER  
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            16
                          
                          
                            00
                          
                          :
                          
                            07
                          
                          :
                          
                            46.53
                          
                          
                            |
                          
                           ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 
                          
                            |
                          
                           skip
                          
                            -
                          
                          grants 
                          
                            user
                          
                          
                            @skip
                          
                          
                            -
                          
                          grants host 
                          
                            |
                          
                           utf8mb4              
                          
                            |
                          
                           utf8mb4_0900_ai_ci   
                          
                            |
                          
                           utf8mb4_0900_ai_ci 
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            ----------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+
                          
                          
                            1
                          
                           row 
                          
                            in
                          
                          
                            set
                          
                           (
                          
                            0.00
                          
                           sec)
                        

    说明2:delimiter是将sql语句终止符由“&”在改为“;” 。

  4.5 插入验证

                          mysql
                          
                            >
                          
                          
                            insert
                          
                          
                            into
                          
                           student(id,name,age,score) 
                          
                            values
                          
                          (
                          
                            null
                          
                          ,"李白",
                          
                            30
                          
                          ,
                          
                            100
                          
                          
                            );
Query OK, 
                          
                          
                            1
                          
                           row affected (
                          
                            0.21
                          
                          
                             sec)

mysql
                          
                          
                            >
                          
                          
                            select
                          
                          
                            *
                          
                          
                            from
                          
                          
                             student_logs;

                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+-----------+---------------------+------------+-----------------------------------------------------------+
                          
                          
                            |
                          
                           id 
                          
                            |
                          
                           operation 
                          
                            |
                          
                           operate_time        
                          
                            |
                          
                           operate_id 
                          
                            |
                          
                           operate_params                                            
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+-----------+---------------------+------------+-----------------------------------------------------------+
                          
                          
                            |
                          
                          
                            1
                          
                          
                            |
                          
                          
                            insert
                          
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            16
                          
                          
                            00
                          
                          :
                          
                            21
                          
                          :
                          
                            47
                          
                          
                            |
                          
                          
                            5
                          
                          
                            |
                          
                           插入的数据内容为:id
                          
                            =
                          
                          5name
                          
                            =
                          
                          李白age
                          
                            =
                          
                          30score
                          
                            =
                          
                          
                            100
                          
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+-----------+---------------------+------------+-----------------------------------------------------------+
                          
                          
                            1
                          
                           row 
                          
                            in
                          
                          
                            set
                          
                           (
                          
                            0.00
                          
                           sec)
                        

    说明3:插入数据李白的信息就已经自动的在student_logs中记录 。

5、修改数据触发器案例

  5.1 创建修改数据触发器

                          mysql
                          
                            >
                          
                          delimiter 
                          
                            &
                          
                          
                            
mysql
                          
                          
                            >
                          
                          
                            create
                          
                          
                            trigger
                          
                           student_update_trigger after 
                          
                            update
                          
                          
                            on
                          
                           student 
                          
                            for
                          
                           each row 
                          
                            begin
                          
                          
                            insert
                          
                          
                            into
                          
                           student_logs(id, operation, operate_time,operate_id,operate_params) 
                          
                            values
                          
                           (
                          
                            null
                          
                          ,
                          
                            '
                          
                          
                            update
                          
                          
                            '
                          
                          ,now(),new_id,concat( 
                          
                            '
                          
                          
                            更新前数据:id=
                          
                          
                            '
                          
                          ,old.id,
                          
                            '
                          
                          
                            ,name=
                          
                          
                            '
                          
                          ,old.name,
                          
                            '
                          
                          
                            ,age=
                          
                          
                            '
                          
                          ,old.age,
                          
                            '
                          
                          
                            ,score=
                          
                          
                            '
                          
                          ,old.score,
                          
                            '
                          
                          
                             | 更新后数据:id=
                          
                          
                            '
                          
                          ,new.id, 
                          
                            '
                          
                          
                            ,name=
                          
                          
                            '
                          
                          ,new.name, 
                          
                            '
                          
                          
                            ,age=
                          
                          
                            '
                          
                          ,new.age, 
                          
                            '
                          
                          
                            ,score=
                          
                          
                            '
                          
                          ,new.score)); 
                          
                            end
                          
                          ; 
                          
                            &
                          
                          
                            
Query OK, 
                          
                          
                            0
                          
                           rows affected (1
                          
                            .86
                          
                          
                             sec)
mysql
                          
                          
                            >
                          
                           delimiter ;
                        

  5.2 查看创建的触发器

                          mysql
                          
                            >
                          
                          
                             show triggers;

                          
                          
                            +
                          
                          
                            --
                          
                          
                            ----------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+
                          
                          
                            |
                          
                          
                            Trigger
                          
                          
                            |
                          
                           Event  
                          
                            |
                          
                          
                            Table
                          
                          
                            |
                          
                           Statement                                                                                                                                                                                                                                                                                                               
                          
                            |
                          
                           Timing 
                          
                            |
                          
                           Created                
                          
                            |
                          
                           sql_mode                                                                                                              
                          
                            |
                          
                           Definer                           
                          
                            |
                          
                           character_set_client 
                          
                            |
                          
                           collation_connection 
                          
                            |
                          
                          
                            Database
                          
                           Collation 
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            ----------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+
                          
                          
                            |
                          
                           student_insert_trigger 
                          
                            |
                          
                          
                            INSERT
                          
                          
                            |
                          
                           student 
                          
                            |
                          
                          
                            begin
                          
                          
                            insert
                          
                          
                            into
                          
                           student_logs(id,operation,operate_time,operate_id,operate_params) 
                          
                            values
                          
                           (
                          
                            null
                          
                          , 
                          
                            '
                          
                          
                            insert
                          
                          
                            '
                          
                          , now(), new.id, concat("插入的数据内容为:id
                          
                            =
                          
                          ",new.id,"name
                          
                            =
                          
                          ",new.name,"age
                          
                            =
                          
                          ",new.age,"score
                          
                            =
                          
                          ",new.score)); 
                          
                            end
                          
                          
                            |
                          
                           AFTER  
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            16
                          
                          
                            00
                          
                          :
                          
                            07
                          
                          :
                          
                            46.53
                          
                          
                            |
                          
                           ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 
                          
                            |
                          
                           skip
                          
                            -
                          
                          grants 
                          
                            user
                          
                          
                            @skip
                          
                          
                            -
                          
                          grants host 
                          
                            |
                          
                           utf8mb4              
                          
                            |
                          
                           utf8mb4_0900_ai_ci   
                          
                            |
                          
                           utf8mb4_0900_ai_ci 
                          
                            |
                          
                          
                            |
                          
                           student_update_trigger 
                          
                            |
                          
                          
                            UPDATE
                          
                          
                            |
                          
                           student 
                          
                            |
                          
                          
                            begin
                          
                          
                            insert
                          
                          
                            into
                          
                           student_logs(id, operation, operate_time,operate_id,operate_params) 
                          
                            values
                          
                           (
                          
                            null
                          
                          ,
                          
                            '
                          
                          
                            update
                          
                          
                            '
                          
                          ,now(),new.id,concat( 
                          
                            '
                          
                          
                            更新前数据:id=
                          
                          
                            '
                          
                          ,old.id,
                          
                            '
                          
                          
                            ,name=
                          
                          
                            '
                          
                          ,old.name,
                          
                            '
                          
                          
                            ,age=
                          
                          
                            '
                          
                          ,old.age,
                          
                            '
                          
                          
                            ,score=
                          
                          
                            '
                          
                          ,old.score,
                          
                            '
                          
                          
                             | 更新后数据:id=
                          
                          
                            '
                          
                          ,new.id, 
                          
                            '
                          
                          
                            ,name=
                          
                          
                            '
                          
                          ,new.name, 
                          
                            '
                          
                          
                            ,age=
                          
                          
                            '
                          
                          ,new.age, 
                          
                            '
                          
                          
                            ,score=
                          
                          
                            '
                          
                          ,new.score)); 
                          
                            end
                          
                          
                            |
                          
                           AFTER  
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            16
                          
                          
                            23
                          
                          :
                          
                            38
                          
                          :
                          
                            26.36
                          
                          
                            |
                          
                           ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 
                          
                            |
                          
                           skip
                          
                            -
                          
                          grants 
                          
                            user
                          
                          
                            @skip
                          
                          
                            -
                          
                          grants host 
                          
                            |
                          
                           utf8mb4              
                          
                            |
                          
                           utf8mb4_0900_ai_ci   
                          
                            |
                          
                           utf8mb4_0900_ai_ci 
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            ----------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+
                          
                          
                            2
                          
                           rows 
                          
                            in
                          
                          
                            set
                          
                           (
                          
                            1.16
                          
                           sec)
                        

  5.3 查看student表原始数据

                          mysql
                          
                            >
                          
                          
                            select
                          
                          
                            *
                          
                          
                            from
                          
                          
                             student;

                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+--------+------+-------+
                          
                          
                            |
                          
                           id 
                          
                            |
                          
                           name   
                          
                            |
                          
                           age  
                          
                            |
                          
                           score 
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+--------+------+-------+
                          
                          
                            |
                          
                          
                            1
                          
                          
                            |
                          
                           张三   
                          
                            |
                          
                          
                            18
                          
                          
                            |
                          
                          
                            88
                          
                          
                            |
                          
                          
                            |
                          
                          
                            2
                          
                          
                            |
                          
                           李四   
                          
                            |
                          
                          
                            21
                          
                          
                            |
                          
                          
                            83
                          
                          
                            |
                          
                          
                            |
                          
                          
                            3
                          
                          
                            |
                          
                           王五   
                          
                            |
                          
                          
                            24
                          
                          
                            |
                          
                          
                            76
                          
                          
                            |
                          
                          
                            |
                          
                          
                            4
                          
                          
                            |
                          
                           赵六   
                          
                            |
                          
                          
                            19
                          
                          
                            |
                          
                          
                            94
                          
                          
                            |
                          
                          
                            |
                          
                          
                            5
                          
                          
                            |
                          
                           李白   
                          
                            |
                          
                          
                            30
                          
                          
                            |
                          
                          
                            100
                          
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+--------+------+-------+
                          
                          
                            5
                          
                           rows 
                          
                            in
                          
                          
                            set
                          
                           (
                          
                            1.30
                          
                           sec)
                        

  5.4 修改student表,验证触发器

                          mysql
                          
                            >
                          
                          
                            update
                          
                           student 
                          
                            set
                          
                           age
                          
                            =
                          
                          
                            25
                          
                          
                            where
                          
                           id 
                          
                            =
                          
                          
                            5
                          
                          
                            ;
Query OK, 
                          
                          
                            1
                          
                           row affected (
                          
                            0.38
                          
                          
                             sec)
Rows matched: 
                          
                          
                            1
                          
                            Changed: 
                          
                            1
                          
                            Warnings: 
                          
                            0
                          
                        

  5.5 查看修改后的student表和student_logs表,验证触发器

                          mysql
                          
                            >
                          
                          
                            select
                          
                          
                            *
                          
                          
                            from
                          
                          
                             student;

                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+--------+------+-------+
                          
                          
                            |
                          
                           id 
                          
                            |
                          
                           name   
                          
                            |
                          
                           age  
                          
                            |
                          
                           score 
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+--------+------+-------+
                          
                          
                            |
                          
                          
                            1
                          
                          
                            |
                          
                           张三   
                          
                            |
                          
                          
                            18
                          
                          
                            |
                          
                          
                            88
                          
                          
                            |
                          
                          
                            |
                          
                          
                            2
                          
                          
                            |
                          
                           李四   
                          
                            |
                          
                          
                            21
                          
                          
                            |
                          
                          
                            83
                          
                          
                            |
                          
                          
                            |
                          
                          
                            3
                          
                          
                            |
                          
                           王五   
                          
                            |
                          
                          
                            24
                          
                          
                            |
                          
                          
                            76
                          
                          
                            |
                          
                          
                            |
                          
                          
                            4
                          
                          
                            |
                          
                           赵六   
                          
                            |
                          
                          
                            19
                          
                          
                            |
                          
                          
                            94
                          
                          
                            |
                          
                          
                            |
                          
                          
                            5
                          
                          
                            |
                          
                           李白   
                          
                            |
                          
                          
                            25
                          
                          
                            |
                          
                          
                            100
                          
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+--------+------+-------+
                          
                          
                            5
                          
                           rows 
                          
                            in
                          
                          
                            set
                          
                           (
                          
                            0.00
                          
                          
                             sec)

mysql
                          
                          
                            >
                          
                          
                            select
                          
                          
                            *
                          
                          
                            from
                          
                          
                             student_logs;

                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+
                          
                          
                            |
                          
                           id 
                          
                            |
                          
                           operation 
                          
                            |
                          
                           operate_time        
                          
                            |
                          
                           operate_id 
                          
                            |
                          
                           operate_params                                                                                            
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+
                          
                          
                            |
                          
                          
                            1
                          
                          
                            |
                          
                          
                            insert
                          
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            16
                          
                          
                            00
                          
                          :
                          
                            21
                          
                          :
                          
                            47
                          
                          
                            |
                          
                          
                            5
                          
                          
                            |
                          
                           插入的数据内容为:id
                          
                            =
                          
                          5name
                          
                            =
                          
                          李白age
                          
                            =
                          
                          30score
                          
                            =
                          
                          
                            100
                          
                          
                            |
                          
                          
                            |
                          
                          
                            2
                          
                          
                            |
                          
                          
                            update
                          
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            16
                          
                          
                            23
                          
                          :
                          
                            58
                          
                          :
                          
                            23
                          
                          
                            |
                          
                          
                            5
                          
                          
                            |
                          
                           更新前数据:id
                          
                            =
                          
                          
                            5
                          
                          ,name
                          
                            =
                          
                          李白,age
                          
                            =
                          
                          
                            30
                          
                          ,score
                          
                            =
                          
                          
                            100
                          
                          
                            |
                          
                           更新后数据:id
                          
                            =
                          
                          
                            5
                          
                          ,name
                          
                            =
                          
                          李白,age
                          
                            =
                          
                          
                            25
                          
                          ,score
                          
                            =
                          
                          
                            100
                          
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+
                          
                          
                            2
                          
                           rows 
                          
                            in
                          
                          
                            set
                          
                           (
                          
                            0.00
                          
                           sec)
                        

   。

6、删除数据触发器案例

  6.1 创建删除数据触发器

                          mysql
                          
                            >
                          
                          delimiter 
                          
                            &
                          
                          
                            
mysql
                          
                          
                            >
                          
                          
                            create
                          
                          
                            trigger
                          
                           student_delete_trigger after 
                          
                            delete
                          
                          
                            on
                          
                           student 
                          
                            for
                          
                           each row 
                          
                            begin
                          
                          
                            insert
                          
                          
                            into
                          
                           student_logs(id,operation,operate_time,operate_id,operate_params) 
                          
                            values
                          
                           (
                          
                            null
                          
                          ,
                          
                            '
                          
                          
                            delete
                          
                          
                            '
                          
                          ,now(),old.id, concat(
                          
                            '
                          
                          
                            删除之前的数据:id=
                          
                          
                            '
                          
                          ,old.id, 
                          
                            '
                          
                          
                            ,name=
                          
                          
                            '
                          
                          ,old.name,
                          
                            '
                          
                          
                            ,age=
                          
                          
                            '
                          
                          ,old.age,
                          
                            '
                          
                          
                            ,score=
                          
                          
                            '
                          
                          ,old.score)); 
                          
                            end
                          
                          ;
                          
                            &
                          
                          
                            
Query OK, 
                          
                          
                            0
                          
                           rows affected (
                          
                            0.87
                          
                           sec)
                        

  6.2 查看触发器

                          mysql
                          
                            >
                          
                          
                             show triggers;
    
                          
                          
                            ->
                          
                          
                            &
                          
                          
                            +
                          
                          
                            --
                          
                          
                            ----------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+
                          
                          
                            |
                          
                          
                            Trigger
                          
                          
                            |
                          
                           Event  
                          
                            |
                          
                          
                            Table
                          
                          
                            |
                          
                           Statement                                                                                                                                                                                                                                                                                                               
                          
                            |
                          
                           Timing 
                          
                            |
                          
                           Created                
                          
                            |
                          
                           sql_mode                                                                                                              
                          
                            |
                          
                           Definer                           
                          
                            |
                          
                           character_set_client 
                          
                            |
                          
                           collation_connection 
                          
                            |
                          
                          
                            Database
                          
                           Collation 
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            ----------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+
                          
                          
                            |
                          
                           student_insert_trigger 
                          
                            |
                          
                          
                            INSERT
                          
                          
                            |
                          
                           student 
                          
                            |
                          
                          
                            begin
                          
                          
                            insert
                          
                          
                            into
                          
                           student_logs(id,operation,operate_time,operate_id,operate_params) 
                          
                            values
                          
                           (
                          
                            null
                          
                          , 
                          
                            '
                          
                          
                            insert
                          
                          
                            '
                          
                          , now(), new.id, concat("插入的数据内容为:id
                          
                            =
                          
                          ",new.id,"name
                          
                            =
                          
                          ",new.name,"age
                          
                            =
                          
                          ",new.age,"score
                          
                            =
                          
                          ",new.score)); 
                          
                            end
                          
                          
                            |
                          
                           AFTER  
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            16
                          
                          
                            00
                          
                          :
                          
                            07
                          
                          :
                          
                            46.53
                          
                          
                            |
                          
                           ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 
                          
                            |
                          
                           skip
                          
                            -
                          
                          grants 
                          
                            user
                          
                          
                            @skip
                          
                          
                            -
                          
                          grants host 
                          
                            |
                          
                           utf8mb4              
                          
                            |
                          
                           utf8mb4_0900_ai_ci   
                          
                            |
                          
                           utf8mb4_0900_ai_ci 
                          
                            |
                          
                          
                            |
                          
                           student_update_trigger 
                          
                            |
                          
                          
                            UPDATE
                          
                          
                            |
                          
                           student 
                          
                            |
                          
                          
                            begin
                          
                          
                            insert
                          
                          
                            into
                          
                           student_logs(id, operation, operate_time,operate_id,operate_params) 
                          
                            values
                          
                           (
                          
                            null
                          
                          ,
                          
                            '
                          
                          
                            update
                          
                          
                            '
                          
                          ,now(),new.id,concat( 
                          
                            '
                          
                          
                            更新前数据:id=
                          
                          
                            '
                          
                          ,old.id,
                          
                            '
                          
                          
                            ,name=
                          
                          
                            '
                          
                          ,old.name,
                          
                            '
                          
                          
                            ,age=
                          
                          
                            '
                          
                          ,old.age,
                          
                            '
                          
                          
                            ,score=
                          
                          
                            '
                          
                          ,old.score,
                          
                            '
                          
                          
                             | 更新后数据:id=
                          
                          
                            '
                          
                          ,new.id, 
                          
                            '
                          
                          
                            ,name=
                          
                          
                            '
                          
                          ,new.name, 
                          
                            '
                          
                          
                            ,age=
                          
                          
                            '
                          
                          ,new.age, 
                          
                            '
                          
                          
                            ,score=
                          
                          
                            '
                          
                          ,new.score)); 
                          
                            end
                          
                          
                            |
                          
                           AFTER  
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            16
                          
                          
                            23
                          
                          :
                          
                            57
                          
                          :
                          
                            34.20
                          
                          
                            |
                          
                           ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 
                          
                            |
                          
                           skip
                          
                            -
                          
                          grants 
                          
                            user
                          
                          
                            @skip
                          
                          
                            -
                          
                          grants host 
                          
                            |
                          
                           utf8mb4              
                          
                            |
                          
                           utf8mb4_0900_ai_ci   
                          
                            |
                          
                           utf8mb4_0900_ai_ci 
                          
                            |
                          
                          
                            |
                          
                           student_delete_trigger 
                          
                            |
                          
                          
                            DELETE
                          
                          
                            |
                          
                           student 
                          
                            |
                          
                          
                            begin
                          
                          
                            insert
                          
                          
                            into
                          
                           student_logs(id,operation,operate_time,operate_id,operate_params) 
                          
                            values
                          
                           (
                          
                            null
                          
                          ,
                          
                            '
                          
                          
                            delete
                          
                          
                            '
                          
                          ,now(),old.id, concat(
                          
                            '
                          
                          
                            删除之前的数据:id=
                          
                          
                            '
                          
                          ,old.id, 
                          
                            '
                          
                          
                            ,name=
                          
                          
                            '
                          
                          ,old.name,
                          
                            '
                          
                          
                            ,age=
                          
                          
                            '
                          
                          ,old.age,
                          
                            '
                          
                          
                            ,score=
                          
                          
                            '
                          
                          ,old.score)); 
                          
                            end
                          
                          
                            |
                          
                           AFTER  
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            17
                          
                          
                            00
                          
                          :
                          
                            15
                          
                          :
                          
                            46.44
                          
                          
                            |
                          
                           ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 
                          
                            |
                          
                           skip
                          
                            -
                          
                          grants 
                          
                            user
                          
                          
                            @skip
                          
                          
                            -
                          
                          grants host 
                          
                            |
                          
                           utf8mb4              
                          
                            |
                          
                           utf8mb4_0900_ai_ci   
                          
                            |
                          
                           utf8mb4_0900_ai_ci 
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            ----------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+
                          
                          
                            3
                          
                           rows 
                          
                            in
                          
                          
                            set
                          
                           (
                          
                            0.00
                          
                           sec)
                        

  6.3 删除数据

                          mysql
                          
                            >
                          
                          
                             delimiter ;
mysql
                          
                          
                            >
                          
                          
                            delete
                          
                          
                            from
                          
                           student 
                          
                            where
                          
                           id 
                          
                            =
                          
                          
                            5
                          
                          
                            ;
Query OK, 
                          
                          
                            1
                          
                           row affected (
                          
                            0.70
                          
                           sec)
                        

  6.4 查看student表和student_logs表验证触发器

                          mysql
                          
                            >
                          
                          
                            select
                          
                          
                            *
                          
                          
                            from
                          
                          
                             student;

                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+--------+------+-------+
                          
                          
                            |
                          
                           id 
                          
                            |
                          
                           name   
                          
                            |
                          
                           age  
                          
                            |
                          
                           score 
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+--------+------+-------+
                          
                          
                            |
                          
                          
                            1
                          
                          
                            |
                          
                           张三   
                          
                            |
                          
                          
                            18
                          
                          
                            |
                          
                          
                            88
                          
                          
                            |
                          
                          
                            |
                          
                          
                            2
                          
                          
                            |
                          
                           李四   
                          
                            |
                          
                          
                            21
                          
                          
                            |
                          
                          
                            83
                          
                          
                            |
                          
                          
                            |
                          
                          
                            3
                          
                          
                            |
                          
                           王五   
                          
                            |
                          
                          
                            24
                          
                          
                            |
                          
                          
                            76
                          
                          
                            |
                          
                          
                            |
                          
                          
                            4
                          
                          
                            |
                          
                           赵六   
                          
                            |
                          
                          
                            19
                          
                          
                            |
                          
                          
                            94
                          
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+--------+------+-------+
                          
                          
                            4
                          
                           rows 
                          
                            in
                          
                          
                            set
                          
                           (
                          
                            0.00
                          
                          
                             sec)

mysql
                          
                          
                            >
                          
                          
                            select
                          
                          
                            *
                          
                          
                            from
                          
                          
                             student_logs;

                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+
                          
                          
                            |
                          
                           id 
                          
                            |
                          
                           operation 
                          
                            |
                          
                           operate_time        
                          
                            |
                          
                           operate_id 
                          
                            |
                          
                           operate_params                                                                                            
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+
                          
                          
                            |
                          
                          
                            1
                          
                          
                            |
                          
                          
                            insert
                          
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            16
                          
                          
                            00
                          
                          :
                          
                            21
                          
                          :
                          
                            47
                          
                          
                            |
                          
                          
                            5
                          
                          
                            |
                          
                           插入的数据内容为:id
                          
                            =
                          
                          5name
                          
                            =
                          
                          李白age
                          
                            =
                          
                          30score
                          
                            =
                          
                          
                            100
                          
                          
                            |
                          
                          
                            |
                          
                          
                            2
                          
                          
                            |
                          
                          
                            update
                          
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            16
                          
                          
                            23
                          
                          :
                          
                            58
                          
                          :
                          
                            23
                          
                          
                            |
                          
                          
                            5
                          
                          
                            |
                          
                           更新前数据:id
                          
                            =
                          
                          
                            5
                          
                          ,name
                          
                            =
                          
                          李白,age
                          
                            =
                          
                          
                            30
                          
                          ,score
                          
                            =
                          
                          
                            100
                          
                          
                            |
                          
                           更新后数据:id
                          
                            =
                          
                          
                            5
                          
                          ,name
                          
                            =
                          
                          李白,age
                          
                            =
                          
                          
                            25
                          
                          ,score
                          
                            =
                          
                          
                            100
                          
                          
                            |
                          
                          
                            |
                          
                          
                            3
                          
                          
                            |
                          
                          
                            delete
                          
                          
                            |
                          
                          
                            2023
                          
                          
                            -
                          
                          
                            08
                          
                          
                            -
                          
                          
                            17
                          
                          
                            00
                          
                          :
                          
                            18
                          
                          :
                          
                            06
                          
                          
                            |
                          
                          
                            5
                          
                          
                            |
                          
                           删除之前的数据:id
                          
                            =
                          
                          
                            5
                          
                          ,name
                          
                            =
                          
                          李白,age
                          
                            =
                          
                          
                            25
                          
                          ,score
                          
                            =
                          
                          
                            100
                          
                          
                            |
                          
                          
                            +
                          
                          
                            --
                          
                          
                            --+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+
                          
                          
                            3
                          
                           rows 
                          
                            in
                          
                          
                            set
                          
                           (
                          
                            0.00
                          
                           sec)
                        

  。

最后此篇关于Mysql高级8-触发器的文章就讲到这里了,如果你想了解更多关于Mysql高级8-触发器的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com