亲测添加数据库中,添加a表中记录,b表中相关数据跟随变化。mysql
提示:demiliter更换mysql结束符。由 ;变成$$(直接delimiter $$(直接回车))。sql
插入触发器写法:数据库
mysql> create trigger tr2 -> after insert on o -> for each row -> begin -> update g set num=num-new.much where gid=new.gid; -> end$$
数据库结构(以下图)测试
a\b表分别为g\o表数据命令行
如下为运行mysql命令行测试数据(可借鉴)3d
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| source |
| test |
+--------------------+
4 rows in set (0.00 sec)code
mysql> use source
Database changed
mysql> show tables
-> ;
+------------------+
| Tables_in_source |
+------------------+
| db_score |
| db_user |
| g |
| o |
+------------------+
4 rows in set (0.00 sec)orm
mysql> select * from g;
+-----+--------+------+
| gid | name | num |
+-----+--------+------+
| 1 | 闉嬪瓙 | 25 |
| 2 | 澶栧 | 30 |
| 3 | 鎵嬪 | 40 |
| 4 | 瑁ゅ瓙 | 19 |
+-----+--------+------+
4 rows in set (0.00 sec)blog
mysql> select * from o;
+-----+------+------+
| oid | gid | much |
+-----+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 4 | 2 |
| 6 | 4 | 1 |
+-----+------+------+
4 rows in set (0.00 sec)it
mysql> delimiter $$
mysql> select * from o;
-> $$
+-----+------+------+
| oid | gid | much |
+-----+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 4 | 2 |
| 6 | 4 | 1 |
+-----+------+------+
4 rows in set (0.00 sec)
mysql> create trigger tr2
-> after insert on o
-> for each row
-> begin
-> update g set num=num-new.much where gid=new.gid;
-> end$$
Query OK, 0 rows affected (0.11 sec)
mysql> insert o values(7,1,2)$$
Query OK, 1 row affected (0.10 sec)
mysql> select * from g
-> $$
+-----+--------+------+
| gid | name | num |
+-----+--------+------+
| 1 | 闉嬪瓙 | 23 |
| 2 | 澶栧 | 30 |
| 3 | 鎵嬪 | 40 |
| 4 | 瑁ゅ瓙 | 19 |
+-----+--------+------+
4 rows in set (0.00 sec)
mysql>
如下为navicat触发器呈现(可仿着写)
删除触发器写法(简写)
mysql> create trigger tr3 -> after delete on o -> for each row -> begin -> update g set num=num-old.much where gid=old.gid; -> end$$
navicat删除触发器写法