Geek Answers Handbook

Trigger Error, MySQL

Well, I researched and did not find a solution to the following problem 'SQL Error (1442): Can not update table' messages' in stored function / trigger because it is already used by statement Which invoked this stored function / trigger.`

My trigger is created only when I run INSERT in the message table, this error occurs, my trigger

DELIMITER $$ 
DROP TRIGGER IF EXISTS `onMessage` ;
CREATE TRIGGER `onMessage` BEFORE INSERT ON `messages` FOR EACH ROW 
BEGIN
       UPDATE `users` SET `users`.`messages` = ( `users`.`messages` + 1 ) 
       WHERE `users`.`uid` = NEW.uid ;
       DELETE FROM `messages` WHERE `date` < ( NOW( ) - INTERVAL 1 MINUTE ) ;
END ;
$$
DELIMITER ;
+2
source share
3 answers

This is a limitation of the use of triggers.

From MySql documentation :

Inside a stored function or trigger, it is not allowed to modify a table that is already in use (for reading or writing) by the operator invoking the function or trigger.

+4
source

Right, for MySQL anyway

, . , , MySQL (RBAR)

INSERT messages

+2

It is not possible to update the table for which the trigger is created in the trigger, because Mysql does some blocking elements in this table. Therefore, you cannot insert / update / delete rows of the same table, because the trigger called again and again, ending with recursion.

If you want to create a trigger in the table that will be updated (in this case, deletion will be performed), make sure that you use the NEW.column_namelink to the row after updating it.

0
source

More articles:


All Articles