mysql触发器实验例题_mysql触发器小实验

mysql> usetest;Databasechanged

mysql> desctime;+-------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+-------+

| id | bigint(60) unsigned | NO | | 0 | |

+-------+---------------------+------+-----+---------+-------+

1 row in set (0.01sec)

mysql> desctime_2;+-------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+-------+

| id | bigint(60) unsigned | NO | | 0 | |

+-------+---------------------+------+-----+---------+-------+

1 row in set (0.01sec)

#创建触发器 当 time表插入一条的时候 time_2表也插入这条新增的数据

mysql>delimiter $$

mysql> create triggert_afterinsert_on_time-> after insert on time foreach row-> begin

-> insert into time_2(id) values(new.id);-> end

->$$

Query OK,0 rows affected (0.14sec)

mysql> insert into time values (100);->$$

Query OK,1 row affected (0.13sec)

mysql> select * fromtime;$$+-----+

| id |

+-----+

| 100 |

+-----+

1 row in set (0.00sec)

mysql> select * fromtime_2;$$+-----+

| id |

+-----+

| 100 |

+-----+

1 row in set (0.00sec)

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.01sec)

#创建触发器 当 time表删除一条的时候 time_2表也删除这条数据

mysql> create triggert_afterdelete_on_time-> after delete on time foreach row-> begin

-> delete from time_2 where id=old.id;-> end

->$$

Query OK,0 rows affected (0.14sec)

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.01sec)

mysql> delete from time where id=100;$$

Query OK,1 row affected (0.09sec)

mysql> select * fromtime;$$

Emptyset (0.00sec)

mysql> select * fromtime_2;$$

Emptyset (0.00sec)

mysql> exit