How to UPDATE ALL OLD ROWS in A TABLE with AFTER/BEFORE INSERT ON trigger IN THE SAME TABLE in mysql?

Question

I need to update the status column of all the old rows of a table to false before/after inserting a new row into that table.

I have visited these but didn't solve my query. MySQL - Trigger for updating same table after insert

DELIMITER $$
DROP TRIGGER update_old_status $$
CREATE TRIGGER update_old_status
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    DECLARE MYCOUNTER INT;
    SELECT COUNT(*) INTO MYCOUNTER FROM table_name;

    IF (MYCOUNTER > 0) THEN
        UPDATE table_name SET status = false WHERE status = true;
    END IF;

END $$
DELIMITER ;

Here what i have as error message:

Caused by: java.sql.SQLException: Can't update table 'table_name' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

score 1 · Accepted Answer · answered Apr 11 '19 at 19:01

You can't use a trigger to update the same table for which the trigger is running, because there's too high a risk of creating an infinite loop.

For example, an insert trigger updates its table, which runs another trigger on update, that inserts to the same table, which runs the insert trigger...

You can't and don't need to use a trigger for this. Instead, use a transaction and run the UPDATE before your INSERT in your application.

Pseudocode:

START TRANSACTION;
UPDATE table_name SET status = false;
INSERT INTO table_name ...values...
COMMIT;

yeah, totally satisfied with what you are saying...thank you @Bill — Space, Apr 12 '19 at 02:23

How to UPDATE ALL OLD ROWS in A TABLE with AFTER/BEFORE INSERT ON trigger IN THE SAME TABLE in mysql?

1 Answers1