How to keep data updated in two tables?

Question

How to keep data updated in two tables?

Navigation

#1 by (1 votes)

2

There are two tables that should be kept up to date in different banks, "User" database "DB1" and "User" from the "DB2". In this case, it would be the "password" field to be updated. I have a Trigger BEFORE UPDATE in the database "BD1" that is functioning normally updating data in 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 not update table 'User' in stored function / trigger because it is already used by statement Which Invoked this stored function / trigger
.

The "why" of this error I already know. But then I ask for you, is there any other way to update both the DB1 as DB2, keeping data "password" the same?

mysql sql-update

asked by anonymous 13.03.2015 / 21:55

1 answer

Quit get request as soon as you start another Custom Sound in Cordova Local Notification

score 1 · Answer 1

The problem is that a trigger triggers the other, perhaps creating a "source" field you can handle 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;