MySQL触发器

触发器：触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

语法

create trigger	触发器名	触发时间	触发事件	on 表名	for each now
触发器状态

①MySQL的触发事件有三种：insert、update和delete;

• insert：将新纪录插入表时激活触发程序，例如：通过insert、load、data和replace语句可以激活触发程序运行。

• update：更改某一行记录时激活触发程序，例如：通过update语句可以激活触发程序运行。

• delete：从表中删除某一行记录时触发激活程序，例如，通过delete和replace语句可以激活触发程序运行。

②触发器的触发时间：before和after

​ before表示在触发事件发生之前执行触发程序，after表示在触发事件发生之后执行触发程序。

③for each row表示行级触发

​ for each row表示更新(insert、update或者delete)操作影响的每一条记录都会执行一次触发程序。

④new,old关键字：触发程序中可以使用old，new关键字

• 向表中插入新纪录时，在触发程序中可以使用new关键字表示新纪录。当需要访问新纪录的某个字段时，可以使用"new.字段名"的方式访问。

• 当从表中删除某条记录时，在触发程序中可以使用old关键字表示旧纪录。当需要访问旧纪录的某个字段时，可以使用"old.字段名"的方式访问。

• old记录是只读的，可以引用，不能修改。在before触发程序中，可使用"set new.字段名=value"更改new记录的值。after触发程序中，则不可以。

mysql> select * from student;
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
|    1 | bob   | M      |
|    2 | alice | F      |
|    3 | john  | F      |
+------+-------+--------+
3 rows in set (0.00 sec)

mysql> select * from class;
+------+----------+
| id   | class_no |
+------+----------+
|    1 | 1        |
|    2 | 1        |
|    3 | 2        |
+------+----------+
3 rows in set (0.00 sec)

例：删除student 表数据时，删除class表对应的数据。
mysql> delimiter $$
mysql> create trigger delClass after delete on student for each row
    -> begin
    -> delete from class where id=old.id;
    -> end;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> delete from student where id=3;
    -> $$
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
    -> $$
+------+----------+
| id   | class_no |
+------+----------+
|    1 | 1        |
|    2 | 1        |
+------+----------+

查看触发器状态

mysql> show triggers \G
*************************** 1. row ***************************
             Trigger: delClass
               Event: DELETE
               Table: student
           Statement: begin
delete from class where id=old.id;
end
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

通过information_schema查看指定信息

mysql> desc information_schema.triggers;
+----------------------------+---------------+------+-----+---------+-------+
| Field                      | Type          | Null | Key | Default | Extra |
+----------------------------+---------------+------+-----+---------+-------+
| TRIGGER_CATALOG            | varchar(512)  | NO   |     |         |       |
| TRIGGER_SCHEMA             | varchar(64)   | NO   |     |         |       |
| TRIGGER_NAME               | varchar(64)   | NO   |     |         |       |
| EVENT_MANIPULATION         | varchar(6)    | NO   |     |         |       |
| EVENT_OBJECT_CATALOG       | varchar(512)  | NO   |     |         |       |
| EVENT_OBJECT_SCHEMA        | varchar(64)   | NO   |     |         |       |
| EVENT_OBJECT_TABLE         | varchar(64)   | NO   |     |         |       |
| ACTION_ORDER               | bigint(4)     | NO   |     | 0       |       |
| ACTION_CONDITION           | longtext      | YES  |     | NULL    |       |
| ACTION_STATEMENT           | longtext      | NO   |     | NULL    |       |
| ACTION_ORIENTATION         | varchar(9)    | NO   |     |         |       |
| ACTION_TIMING              | varchar(6)    | NO   |     |         |       |
| ACTION_REFERENCE_OLD_TABLE | varchar(64)   | YES  |     | NULL    |       |
| ACTION_REFERENCE_NEW_TABLE | varchar(64)   | YES  |     | NULL    |       |
| ACTION_REFERENCE_OLD_ROW   | varchar(3)    | NO   |     |         |       |
| ACTION_REFERENCE_NEW_ROW   | varchar(3)    | NO   |     |         |       |
| CREATED                    | datetime      | YES  |     | NULL    |       |
| SQL_MODE                   | varchar(8192) | NO   |     |         |       |
| DEFINER                    | varchar(77)   | NO   |     |         |       |
| CHARACTER_SET_CLIENT       | varchar(32)   | NO   |     |         |       |
| COLLATION_CONNECTION       | varchar(32)   | NO   |     |         |       |
| DATABASE_COLLATION         | varchar(32)   | NO   |     |         |       |
+----------------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

mysql> select * from information_schema.triggers where trigger_name='delClass'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: student
              TRIGGER_NAME: delClass
        EVENT_MANIPULATION: DELETE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: student
        EVENT_OBJECT_TABLE: student
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: begin
delete from class where id=old.id;
end
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: 
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.16 sec)