How to configure after insert update trigger in Mysql.

Thank you for your help.Could you give me some guidance and advice?

I created a trigger in mysql to start immediately after the data is inserted into tbl_products, but it does not start when I insert the data into tbl_products.

CREATE TRIGGER`Percent`AFTER INSERT ON`tbl_products`
 FOR EACH ROW BEGIN
UPDATE
  tbl_products t1,
  (SELECT id, (rate*number) ASprt
    FROM tbl_products GROUP BY id
  )t2
SET
  t1.option=t2.prt
WHERE
  t1.id = t2.id
END

Instead, running the description in BEGIN~END above from SQL in phpMyAdmin results as expected.(The calculation results prt will be substituted for the tbl_products column option)

Therefore, I think there may be a problem with how to create a trigger on mysql, but could you tell me why I can't start the trigger even if I insert the data into tbl_products?

Using a Separate Table

CREATE TRIGGER`Percent`AFTER INSERT ON`tbl_products`
 FOR EACH ROW BEGIN
UPDATE
  tbl_product_rate t1,
  (SELECT tbl_products.id, (rate*number) AS prt
    FROM tbl_products left join tbl_products_rate on tbl_products.id = tbl_products_rate.id GROUP BY id
  )t2
SET
  t1.option=t2.prt
WHERE
  t1.id = t2.id
END

mysql

2022-09-29 20:25

2 Answers

The MySQL trigger does not allow itself to modify the table on which the trigger is configured.
Is it because it could be a recursive call?

"A stored function or trigger cannot change a table that is already in use (to read or write) by the statement that called that stored function or trigger."
https://dev.mysql.com/doc/refman/5.6/ja/stored-program-restrictions.html

--
Additional
The solution is
·Implement in the application.
·Join another table when writing to another table and selecting.
·Create a table for INSERT, UPDATE, DELETE, use the trigger to perform INSERT, UPDATE, DELETE on this table in the same way, and perform the trigger you want to do more
and so on.

2022-09-29 20:25

I think @keitaro_so's answer is why the trigger does not work.
As a workaround, if the MySQL version is 5.7.6 or later, you may be able to use the generated column.

CREATE TABLE TRANGLE(
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT (sidea*sidea+sideb*sideb))
);
INSERT INTOTriangle(sidea,sideb) VALUES(1,1),(3,4),(6,8);

mysql>SELECT* FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sideec |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+

Reference
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

2022-09-29 20:25

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656