I have the following problem with mysql: I have the table A with a trigger that update a columns of table B when something in A change. This trigger works.

I need te possibility to update a column of A when something in B change, but the second trigger generate the error. I know is recursive, but how can I do it?

exp.

    trigger A:
delimiter $$

CREATE TRIGGER TAU_A
AFTER UPDATE ON table_A FOR EACH ROW
       begin
      IF OLD.to_read <> NEW.to_read THEN
            update table_B
            set is_read=if(new.to_read=1,0,1)
            where id=new.id;
      END IF;
       
END$$

trigger B:
delimiter $$

CREATE TRIGGER TAU_B
AFTER UPDATE ON table_b FOR EACH ROW
       begin
      IF OLD.is_read <> NEW.is_readTHEN
            update table_a
            set to_read=if(new.is_read=1,0,1)
            where id=new.id;
      END IF;
       
END$$

CodePudding user response：

Use user-defined variable, check for chaining update.

DEMO

@check_for_cycle user-defined variable is used for to prevent a cycle. The variable name must be unique over a system (i.e. it must be used in this triggers pack only) and provide interference absence (including another applications which may modify these tables data).

CREATE TABLE t1 (id INT PRIMARY KEY, val INT);
CREATE TABLE t2 (id INT PRIMARY KEY, val INT);
INSERT INTO t1 VALUES (1,1), (2,2);
INSERT INTO t2 VALUES (1,1), (3,3);
SELECT * FROM t1;
SELECT * FROM t2;

CREATE TRIGGER tr_au_t1
AFTER UPDATE ON t1
FOR EACH ROW
BEGIN
    IF @check_for_cycle IS NULL THEN
        SET @check_for_cycle := 1;
        UPDATE t2 SET val = NEW.val   10 WHERE id = NEW.id;
        SET @check_for_cycle := NULL;
    END IF;
END

CREATE TRIGGER tr_au_t2
AFTER UPDATE ON t2
FOR EACH ROW
BEGIN
    IF @check_for_cycle IS NULL THEN
        SET @check_for_cycle := 1;
        UPDATE t1 SET val = NEW.val   100 WHERE id = NEW.id;
        SET @check_for_cycle := NULL;
    END IF;
END

UPDATE t1 SET val = val   1;
SELECT * FROM t1;
SELECT * FROM t2;

UPDATE t2 SET val = val   2;
SELECT * FROM t1;
SELECT * FROM t2;

fiddle

You must check that no error occures in a trigger, and clear the variable in the error handler. Otherwise, when INSERT fails then the trigger breaks and the whole process rollbacks, but the variable value will stay non-cleared (variable assignment is not rollbacked) which will lock trigger action for further updates.