1

this is my trigger:

CREATE TRIGGER update_avatar 
BEFORE INSERT ON `tbl_archivos` FOR EACH ROW
UPDATE `tbl_archivos` 
SET `is_avatar`='0' 
WHERE `tbl_usuarios_id_usuario`=NEW.`tbl_usuarios_id_usuario`

and this is the error:

#1442 - Can't update table 'tbl_archivos' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

i want to update the state "is_avatar" to 0 on upload file, where the id_usuario = the inserted id_usuario.

Whats wrong?

Namphibian
  • 12,046
  • 7
  • 46
  • 76
Ninja Coding
  • 1,357
  • 1
  • 16
  • 26

2 Answers2

0

The error message is self-explanatory. MySQL still doesn't allow to issue any DML statements (insert, update, delete) on a table (in your case tbl_archivos) on which you defined the trigger.

The only thing you can do is to alter the values of columns of a row being inserted/updated in a BEFORE trigger.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • The question appears to be potentially useful use case for triggers. Why is it not allowed? I don't see the rationale behind why the same table being inserted to can't be updated `BEFORE INSERT` or even `AFTER INSERT`. – ADTC Dec 03 '13 at 07:04
  • @ADTC There were already some attempts to figure out this. For example look at this question http://stackoverflow.com/questions/6756454/what-is-the-real-cause-of-mysql-error-1442 – Alexander Myshov Dec 03 '13 at 07:27
  • Apparently a comment in that link suggests that you can indeed update the same table you're inserting to by using a function. – ADTC Dec 03 '13 at 07:35
  • @Myshov Nvm.. I tried it in SQLFiddle. It still gives the same error, even if tried to update through a [function](http://sqlfiddle.com/#!2/5e9a6c) or [procedure](http://sqlfiddle.com/#!2/9beaad). – ADTC Dec 03 '13 at 08:13
  • @ADTC For the topic question there is no solutions via triggers as I said already in my answer to the topic. There is no way to use a trigger in MySQL that loops over all rows in a table when activated. Just look at this article please for more details http://mark.koli.ch/2009/07/mysql-trigger-error-1442-hy000-cant-update-table-tbl-in-stored-functiontrigger-because-it-is-al.html – Alexander Myshov Dec 03 '13 at 09:07
0

It doesn't work because you are running an UPDATE inside a INSERT trigger - both statements modify the same table. There doesn't appear to be a way to use a trigger in MySQL that loops over all rows in a table when activated.

It is possible alter the values of columns of a row being modified look at example in this question.

Solution to your problem (as variant) might include building some app to call some procedure, which will update data in your table, at a given interval or it is possible also use a cron job for that.

Community
  • 1
  • 1
Alexander Myshov
  • 2,881
  • 2
  • 20
  • 31
  • Or make a new table named 'preferences' where i define the current avatar. comming from MySql, thats really stupid. But thank you for answer. – Ninja Coding Dec 05 '13 at 03:25