Error: Can't update table 'SampleTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Question

I need to update media cell based on flag value after the insert trigger occurred.

My code is ...

DELIMITER $$
CREATE TRIGGER after_insert AFTER INSERT ON SampleTable
FOR EACH ROW
BEGIN
UPDATE SampleTable SET media = '../trigger/smile1.png' WHERE flag =0;
UPDATE SampleTable SET media = '../trigger/smile2.png' WHERE flag =1;
UPDATE SampleTable SET media = '../trigger/smile3.png' WHERE flag =2;
END$$
DELIMITER ;

Trigger created successfully...

But when I try to insert values, it shows the following error.

"Can't update table 'SampleTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."

I don't understand what is the exact problem.

any help...

If you are trying to update one or more different rows in the same table as the trigger, that is not allowed in my sql — PRABA, Mar 20 '17 at 05:54
first you check with single query in that trigger. remove the other two query. while its working or not. — PRABA, Mar 20 '17 at 05:58
please go through this link http://stackoverflow.com/questions/4547465/updating-table-in-trigger-after-update-on-the-same-table — PRABA, Mar 20 '17 at 06:07

score 1 · Accepted Answer · answered Mar 22 '17 at 11:20

DELIMITER $$
CREATE TRIGGER before_user_insert BEFORE INSERT ON SampleTable
FOR EACH ROW
BEGIN
IF NEW.flag =0 THEN
SET NEW.media = "../trigger/smile1.png";
ELSEIF NEW.flag = 1 THEN  
SET NEW.media = "../trigger/smile2.png";
ELSEIF NEW.flag = 2 THEN
SET NEW.media = "../trigger/smile3.png";
END IF;
END$$
DELIMITER ;

Error: Can't update table 'SampleTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

1 Answers1