mysql触发器小实验

今天实验了一下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
复制代码

 

posted @   李照耀  阅读(630)  评论(0编辑  收藏  举报
编辑推荐:
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示