I set a trigger in phpMyAdmin with MySQL database with AFTER and INSERT:
update table1
set col1= col2
Then when I go and insert a row, this error message shows up:
Error 500 CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1442 Can't update table 'card' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Any ideas?
The error message in this particular case is quite explicit: you are trying to modify the very table in the trigger on which the trigger itself was invoked and this is not allowed in mysql. See mysql documentation on Restrictions on Stored Programs:
A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
If you know that col1 has to have the same value as col2, then set it in the insert statement itself. If the logic is more complicated than this, then use a stored procedure to execute the insert and the subsequent update and leave triggers out of it.