Re: Synchronizing data between different databases/tables

[Peter Brawley]

Your triggers form an endless loop. MySQL Triggers will not allow that or any other form of recursion.

You'll need to interrupt recursion by tracking where inserts, updates & deletes are originating, eg ...

drop table if exists a,b;
create table a(i int,here bool);
create table b like a;

delimiter go
create trigger ains after insert on a for each row
begin
  if new.here=1 then
    insert into b values(new.i,0);
  end if;
end;
go

create trigger bins after insert on b for each row
begin
  if new.here=1 then
    insert into a values(new.i,0);
  end if;
end;
go

delimiter ;

insert into a values(1,1);
insert into b values(2,1);
select * from a;
+------+------+
| i    | here |
+------+------+
|    1 |    1 |
|    2 |    0 |
+------+------+
select * from b;
+------+------+
| i    | here |
+------+------+
|    1 |    1 |
|    2 |    0 |
+------+------+