MYSQL error 1442: Cannot update a table in a trigger/stored procedure

Question

Consider the table TRADE_ORDERS.

Symbol | Type | Count | Settled | Settled_count
------ | -----| ------| --------| -------------
Apple  | BUY  | 100   | NO      |  0
Apple  | BUY  | 50    | NO      |  0

I have written a trigger after_trade_orders_insert that will AFTER INSERT of a new row will try to settle the previous orders depending on the if there are unsettled orders.

Now if I INSERT into TRADE_ORDERS the row with values Apple, SELL, 150, it should settle all the bought shares. So the intended action should bring the table to below state.

Intended Output

Symbol | Type | Count | Settled | Settled_count
------ | -----| ------| --------| -------------
Apple  | BUY  | 100   | YES     |  100
Apple  | BUY  | 50    | YES     |  50
Apple  | SELL | 150   | YES     |  150

I understand how to write code in mysql to achieve. But when I write the code to do that in after_trade_orders_insert, it fails with error

Error Code: 1442. Can't update table 'NSE_FO' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 0.055 sec

I understand what this error is and why it happens.

Question: How do I achieve the intended output?

score 0 · Answer 1 · answered Sep 12 '16 at 07:32

0

you can not update recently inserted row in table by trigger so , you have to use where clause with new keyword.

answered Sep 12 '16 at 07:32

soniabhishek36

59
5

Ok. But is it allowed for me to update any other rows of the same table? – bobdiya Sep 12 '16 at 07:36
exactly, but it can not update newly inserted row for which you are using trigger. – soniabhishek36 Sep 12 '16 at 07:44
If I am intending to update some other rows of the same table. How do I achieve it? – bobdiya Sep 12 '16 at 07:44
Use where clause in the trigger function. – soniabhishek36 Sep 12 '16 at 07:54

score 0 · Accepted Answer · edited May 23 '17 at 12:32

0

We cannot do what we want to do - A Trigger/Stored procedure cannot update another row of the same table as the row, the trigger was called from.

How to achieve it? From an accepted answer on this thread

The typical way to do that, is to create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

edited May 23 '17 at 12:32

Community

1
1

answered Sep 15 '16 at 04:51

bobdiya

151
5
16

MYSQL error 1442: Cannot update a table in a trigger/stored procedure

2 Answers2