当前位置:Gxlcms > 数据库问题 > MySQL-触发器

MySQL-触发器

时间:2021-07-01 10:21:17 帮助过:17人阅读

1、什么叫触发器: 当满足一定的条件以后,它会触发一个动作的执行,trigger 2、触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发、激活从而实现执行 3、触发器是由一定的事件来触发的,对表的增删改操作、不包括查询,查询是没有触发器的 4、生产中一般不通过MySQL中触发器来实现这功能,是通过Java程序、Python程序代码来实现触发器 5、创建触发器: CREATE: [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body 说明: trigger_name:触发器的名称 trigger_time:{ BEFORE | AFTER },表示在事件之前或之后触发 trigger_event::{ INSERT |UPDATE | DELETE },触发的具体事件 tbl_name:该触发器作用在表名 6、查看触发器 SHOW TRIGGERS 查询系统表information_schema.triggers的方式指定查询条件,查看指定的触发器信息。 mysql> USE information_schema; Database changed mysql> SELECT * FROM triggers WHERE 7、删除触发器 DROP TRIGGER trigger_name; 快速的清空表:truncate table student_info

触发器示例

    创建触发器,在向学生表INSERT数据时,学生数增加,DELETE学生时,学生数减少
    MariaDB [hellodb]> use db1
    Database changed
    MariaDB [db1]> CREATE TABLE student_info (
        -> stu_id INT(11) NOT NULL AUTO_INCREMENT,
        -> stu_name VARCHAR(255) DEFAULT NULL,
        -> PRIMARY KEY (stu_id)
        -> );
    CREATE TABLE student_count (
    student_count INT(11) DEFAULT 0
    );
    INSERT INTO student_count VALUES(0);Query OK, 0 rows affected (0.11 sec)

    MariaDB [db1]> CREATE TABLE student_count (
        -> student_count INT(11) DEFAULT 0
        -> );
    Query OK, 0 rows affected (0.01 sec)

    MariaDB [db1]> INSERT INTO student_count VALUES(0);
    Query OK, 1 row affected (0.01 sec)

    MariaDB [db1]> select * from student_info;
    Empty set (0.00 sec)

    创建增加和减少学生数量的触发器
    MariaDB [db1]> CREATE TRIGGER trigger_student_count_insert
        -> AFTER INSERT
        -> ON student_info FOR EACH ROW
        -> UPDATE student_count SET student_count=student_count+1;
    Query OK, 0 rows affected (0.00 sec)

    MariaDB [db1]> CREATE TRIGGER trigger_student_count_delete
        -> AFTER DELETE
        -> ON student_info FOR EACH ROW
        -> UPDATE student_count SET student_count=student_count-1;
    Query OK, 0 rows affected (0.00 sec)

    # 查看创建好的触发器:
    MariaDB [db1]> show triggers\G
    *************************** 1. row ***************************
                 Trigger: trigger_student_count_insert
                   Event: INSERT
                   Table: student_info
               Statement: UPDATE student_count SET student_count=student_count+1
                  Timing: AFTER
                 Created: 2018-10-09 20:27:09.78
                sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                 Definer: root@localhost
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: utf8_general_ci
    *************************** 2. row ***************************
                 Trigger: trigger_student_count_delete
                   Event: DELETE
                   Table: student_info
               Statement: UPDATE student_count SET student_count=student_count-1
                  Timing: AFTER
                 Created: 2018-10-09 20:27:27.31
                sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                 Definer: root@localhost
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: utf8_general_ci
    2 rows in set (0.00 sec)

    查看建好的student_info表,目前是空的
    MariaDB [db1]> select * from student_info;
    Empty set (0.00 sec)

    往里面插入一条数据;
    MariaDB [db1]> insert student_info values(1,‘cobbler‘);
    Query OK, 1 row affected (0.01 sec)

    查看student_info表里面已经有了一条信息
    MariaDB [db1]> select * from student_info;
    +--------+----------+
    | stu_id | stu_name |
    +--------+----------+
    |      1 | cobbler  |
    +--------+----------+
    1 row in set (0.00 sec)
    查看student_count里面的统计数,
    MariaDB [db1]> select * from student_count;
    +---------------+
    | student_count |
    +---------------+
    |             1 |
    +---------------+
    1 row in set (0.00 sec)

MySQL-触发器

标签:char   sql_mode   not   动作   tran   --   orm   UNC   0.00   

人气教程排行