MySQL-トリガ
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)