How to keep data up to date in two tables?

Question

There are two tables that should be kept updated in different databases, "user" of the "DB1" and "user" of the "DB2" database. In this case, it would be the "password" field to be updated. I have a BEFORE UPDATE Trigger in the "BD1" database that is working normally updating data on DB2. As shown below:

IF old.`password` <> new.`password` THEN
    UPDATE  DB2.usuario
    SET     DB2.usuario.`password`  = new.`password`
    WHERE   DB2.usuario.login       = new.login;
END IF; 

But if I create the same trigger in "DB2" to update DB1...

IF old.`password` <> new.`password` THEN
    UPDATE  DB1.usuario
    SET     DB1.usuario.`password`  = new.`password`
    WHERE   DB1.usuario.login       = new.login;
END IF;

the following error occurs:

ERROR 1442: 1442: Can’t update table 'usuario' in stored Function/Trigger because it is already used by statement which Invoked this stored Function/Trigger.

The "why" of this mistake I already know. But then I ask you, is there any other way to update both DB1 and DB2, keeping "password" data equal?

Answer 1

The problem is that one Rigger fires the other, perhaps by creating an "origin" field you can address the issue

IF old.`password` <> new.`password` AND new.origem <> 'DB2' THEN
    UPDATE  DB2.usuario
    SET     DB2.usuario.`password`  = new.`password` ,
            DB2.ORIGEM = 'DB1'
    WHERE   DB2.usuario.login       = new.login;
END IF; 


IF old.`password` <> new.`password` AND new.origem <> 'DB1'  THEN
    UPDATE  DB1.usuario
    SET     DB1.usuario.`password`  = new.`password`,
            DB2.ORIGEM = 'DB2'
    WHERE   DB1.usuario.login       = new.login;
END IF;