今天实验了一下mysql的触发器
mysql> use test;
Database changed
mysql> desc time;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | bigint(60) unsigned | NO | | 0 | |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> desc time_2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | bigint(60) unsigned | NO | | 0 | |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
#创建触发器 当 time表插入一条的时候 time_2表也插入这条新增的数据
mysql> delimiter $$
mysql> create trigger t_afterinsert_on_time
-> after insert on time for each row
-> begin
-> insert into time_2(id) values (new.id);
-> end
-> $$
Query OK, 0 rows affected (0.14 sec)
mysql> insert into time values (100);
-> $$
Query OK, 1 row affected (0.13 sec)
mysql> select * from time;$$
+-----+
| id |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)
mysql> select * from time_2;$$
+-----+
| id |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)
mysql> show triggers;$$
+-----------------------+--------+-------+-----------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+-----------------------+--------+-------+-----------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| t_afterinsert_on_time | INSERT | time | begin
insert into time_2(id) values (new.id);
end | AFTER | NULL | | root@localhost | gbk | gbk_chinese_ci | utf8_general_ci |
+-----------------------+--------+-------+-----------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
#创建触发器 当 time表删除一条的时候 time_2表也删除这条数据
mysql> create trigger t_afterdelete_on_time
-> after delete on time for each row
-> begin
-> delete from time_2 where id=old.id;
-> end
-> $$
Query OK, 0 rows affected (0.14 sec)
mysql> show triggers;
-> $$
+-----------------------+--------+-------+-----------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+-----------------------+--------+-------+-----------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| t_afterinsert_on_time | INSERT | time | begin
insert into time_2(id) values (new.id);
end | AFTER | NULL | | root@localhost | gbk | gbk_chinese_ci | utf8_general_ci |
| t_afterdelete_on_time | DELETE | time | begin
delete from time_2 where id=old.id;
end | AFTER | NULL | | root@localhost | gbk | gbk_chinese_ci | utf8_general_ci |
+-----------------------+--------+-------+-----------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
2 rows in set (0.01 sec)
mysql> delete from time where id=100;$$
Query OK, 1 row affected (0.09 sec)
mysql> select * from time;$$
Empty set (0.00 sec)
mysql> select * from time_2;$$
Empty set (0.00 sec)
mysql> exit
