I have the following 2 tables: table1 and table2 as described below: +-------+--
ID: 3920210 • Letter: I
Question
I have the following 2 tables: table1 and table2 as described below:
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| id | int(5) | NO | PRI | NULL | auto_increment |
| var | int(5) | YES | | NULL | |
+-------+--------+------+-----+---------+----------------+
table2:
> desc table2;
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| id | int(5) | NO | PRI | NULL | auto_increment |
| var1 | int(5) | YES | | NULL | |
| var2 | int(5) | YES | | NULL | |
+-------+--------+------+-----+---------+----------------+
i created a trigger so table2.var2 is calculated using table1.var * table2.var1:
create trigger tot after insert on table2 for each row update table2 set var2 = table1.var * table2.var2;
When I insert into table2 i get an error:
MariaDB [test_db]> insert into table2 (var1)values('5');
ERROR 1442 (HY000): Can't update table 'table2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Is there a way to do it?
Explanation / Answer
You can not update the same table where the trigger is getting executed, however if you use before update you can set the st to 0 by checking the conditions something as