CODEWITHSUNDEEP
mysqlsql
medo ampir
medo ampir

Reputation: 1910

is there any solution for MYSQL Error Code: 1442?

my code is very long so i'm gonna make it simple. i have the following:

table1 --> have a trigger on insert (call function1 to insert into table2)
table2 --> have a trigger on insert (call function2)

function2()
update table1 depending on a query that table1 is related to.

and i get error can't update table table1 in stored function/trigger because it is already used by statement which invoked this....

so is there any solution or workaround for this...

Upvotes: 0

Views: 1505

Answers (1)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

The thing to understand about triggers is they are part of the transaction.

So Insert Table 1, triggers insert table 2 which updates table1, could cause the original insert to bump into itself. Not done triggers with MySQl, in theory this could be okay, if it's attemting to update the record originally inserted, which hasn't actually been committed yet....

Try and avoid the situation is the usual best advice, however if you can't, an intermediate table might work (insert into that and have it trigger changes to Table1 and 2), or better still, instead of using a direct insert , used a stored procedure and have it update tables 1 & 2 cummulativley.

e.g. instead on insert Table1=A, which causes Table2=B which Causes Table1(A) to become C and SP which looks up what B would do to A and insert that in A(c) and the corresponding record in B.

Triggers are great, but don't go mad with them.

Upvotes: 1

Related Questions