0

Hi,

I want to update a column in same table if another column is updated but I'm getting error message.

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

I tried two different approaches but no luck. Same error.

Anyone has a solution?

Thanks

TRY 1:

DELIMITER $$

DROP TRIGGER IF EXISTS `tr_update_student2_middlenames`$$

CREATE TRIGGER `tr_update_student2_middlenames` AFTER UPDATE on `student2`
FOR EACH ROW BEGIN
    IF NEW.`Postcode` = 'Enrolled' THEN
        UPDATE `student2` SET `MiddleNames` = NULL WHERE `id` = row_id;
    END IF;
END$$

DELIMITER;

TRY 2:

DELIMITER $$

DROP TRIGGER IF EXISTS `tr_update_student2_middlenames`$$

CREATE TRIGGER `tr_update_student2_middlenames` AFTER UPDATE on `student2`
FOR EACH ROW BEGIN
    IF NEW.`Postcode` = 'Enrolled' THEN
        CALL sp_update_student2_middlenames(OLD.id);
    END IF;
END$$

DELIMITER;




DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_update_student2_middlenames`$$

CREATE PROCEDURE `sp_update_student2_middlenames`(row_id INT)
BEGIN
    UPDATE `student2` SET `MiddleNames` = NULL WHERE `id` = row_id;
END$$

DELIMITER ;
2
Contributors
1
Reply
3
Views
5 Years
Discussion Span
Last Post by pritaeas
0

The problem is that your UPDATE trigger, triggers the UPDATE trigger ;) (recursively, ad infinitum). As far as I know, MySQL does not allow this.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.