各位靓仔我遇到一个问题，很困扰，茶饭不思，终日惶惶不得，难受难受

问题遇到的现象和发生背景

在联系MySQL 触发器语句，
但 after 和 before 效果一样，该如何破解。

问题相关代码，请勿粘贴截图

我的实验环境，Windows，

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

两个表，

mysql> create table g
    -> (
    -> pid int PRIMARY key AUTO_INCREMENT,
    -> pname varchar(20),
    -> pnum int,
    -> price DECIMAL
    -> )
    -> ;
Query OK, 0 rows affected (1.61 sec)
mysql> show tables;
+--------------------+
| Tables_in_schooldb |
+--------------------+
| g                  |
+--------------------+
1 row in set (0.00 sec)

mysql> create table o
    -> (
    -> oid int primary key AUTO_INCREMENT,
    -> pid int,
    -> much INT
    -> )
    -> ;
Query OK, 0 rows affected (0.91 sec)
mysql> show tables;
+--------------------+
| Tables_in_schooldb |
+--------------------+
| g                  |
| o                  |
+--------------------+
2 rows in set (0.00 sec)

给o表插入数据，

mysql> insert into g (pname,pnum,price)
    -> values
    -> ('毛笔',100,5),
    -> ('宣纸',100,1),
    -> ('墨水',100,10)
    -> ;
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from g;
+-----+-------+------+-------+
| pid | pname | pnum | price |
+-----+-------+------+-------+
|   1 | 毛笔  |  100 |     5 |
|   2 | 宣纸  |  100 |     1 |
|   3 | 墨水  |  100 |    10 |
+-----+-------+------+-------+
3 rows in set (0.00 sec)

建立一个触发器：

mysql> delimiter //
mysql> create trigger limitprice
    -> after update on g
    -> for each ROW
    -> begin
    -> declare mesg varchar(20);
    -> if (new.price-OLD.price)*100/old.price>20 then
    -> select A002 into mesg;
    -> else set mesg='更改成功';
    -> end if;
    -> end //
Query OK, 0 rows affected (0.12 sec)

mysql> show triggers \G
*************************** 1. row ***************************
             Trigger: limitprice
               Event: UPDATE
               Table: g
           Statement: begin
declare mesg varchar(20);
if (new.price-OLD.price)*100/old.price>20 then
select A002 into mesg;
else set mesg='更改成功';
end if;
end
              Timing: AFTER
             Created: 2022-01-21 15:10:49.42
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

运行结果及报错内容

mysql> update g set price=20 where pid=3;
ERROR 1054 (42S22): Unknown column 'A002' in 'field list'
mysql> select  * from g;
+-----+-------+------+-------+
| pid | pname | pnum | price |
+-----+-------+------+-------+
|   1 | 毛笔  |  100 |     5 |
|   2 | 宣纸  |  100 |     1 |
|   3 | 墨水  |  100 |    10 |
+-----+-------+------+-------+
3 rows in set (0.00 sec)

按道理说，trigger 是 after 的，表里的10 应该变成20才对呀！

我的解答思路和尝试过的方法

mysql> show triggers \G
*************************** 1. row ***************************
             Trigger: limitprice
               Event: UPDATE
               Table: g
           Statement: begin
declare mesg varchar(20);
if (new.price-OLD.price)*100/old.price>20 then
select A002 into mesg;
else set mesg='更改成功';
end if;
end
              Timing: AFTER
             Created: 2022-01-21 15:10:49.42
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> update g set price=100 where pid=1;
ERROR 1054 (42S22): Unknown column 'A002' in 'field list'
mysql> select * from g;
+-----+-------+------+-------+
| pid | pname | pnum | price |
+-----+-------+------+-------+
|   1 | 毛笔  |  100 |     5 |
|   2 | 宣纸  |  100 |     1 |
|   3 | 墨水  |  100 |    10 |
+-----+-------+------+-------+
3 rows in set (0.02 sec)

但就是不变呀，它应该变的呀，报错归报错，但我写的是after呀，update 在 trigger 之前啊，值应该变啊，

我想要达到的结果

关键是，我看教学视频中，teacher 和我一样的语句，表中的值变化了啊，然后，从 after 改成 before 之后，再 update 值才不会变的，为啥，why，为啥啊，欺负人么？
January the 20th 2022 Friday

这么大个报错没看到么？

ERROR 1054 (42S22): Unknown column 'A002' in 'field list'

由于触发器执行失败,你这个事务都是执行不成功的,相当于你啥都没执行。
就算你把这个报错的位置改正确,你这里的after和before也不会有区别,因为你根本就没在触发器里再去查表里面的数据,也没对new进行赋值,全部都只是查old和new的变量,这两个值是多少不会受after和before的影响

首先，你得确定你跟老师的环境一样
比如老师的数据库到底是mysql吗
mysql版本一样吗
否则你按老师敲的代码执行，然后去对比结果，那就对比了个寂寞

mysql> delimiter //
mysql> create trigger limitprice
-> after update on g
-> for each ROW
-> begin
-> declare mesg varchar(20);
-> if (new.price-OLD.price)*100/old.price>20 then
-> select A002 into mesg;
-> else set mesg='更改成功';
-> end if;
-> end //
Query OK, 0 rows affected (0.12 sec)
ERROR 1054 (42S22): Unknown column 'A002' in 'field list' 这么大的一个错误你看不到？而且 new.price-OLD.price,new喝OLD从哪冒出来的？select A002 into mesg，select A002,A002又是从哪冒出来的，无中生有嘛