Trigger on Multiple Mysql Database tables using php

Question

If I have a table t1 in db1 and t2 in db2 . now on any operation on t1 table of db1 i want the same operation to be performed on t2 of db2.

Consider a scenario...if i insert in t1 same record should get added on t2. db1 and db2 both are situated on same database.

can anyone tell what specific steps i should do to fulfill this scenario...or how to open both the database connections before firing trigger?

Look at http://stackoverflow.com/questions/1832759/creating-trigger-across-different-databases — victorsavu3, Sep 03 '12 at 09:35

Omesh · Answer 1 · 2012-09-03T10:00:26.403

0

You can write INSERT, UPDATE & DELETE triggers on one table to reflect data on other table.

Manual here

CREATE TRIGGER insert_t1 BEFORE INSERT ON db1.t1
delimiter //
FOR EACH ROW
BEGIN
    INSERT INTO db2.t2 VALUES (...);

END;//
delimiter ;

edited Sep 03 '12 at 10:00

answered Sep 03 '12 at 09:39

Omesh

27,801
6
42
51

Thank You So Much...Its working fine but if i want to set this for multiple servers is it possible? – Jhanvi Sep 03 '12 at 12:41
You're welcome! :) Yes it is possible to synchronize table data across multiple servers, for that you need to setup replication between those two servers and add that table in replication or other simple option would be to create a `FEDERATED` table pointing to other server and just replace it's name in same trigger above. – Omesh Sep 03 '12 at 18:09

score 0 · Answer 2 · edited Sep 15 '12 at 18:41

This is also a way we can update the data of different databases of the same server according to the changes applied on related database

USE db1;
DROP TRIGGER IF EXISTS t1;

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `t1`  AFTER INSERT
    ON `db1`.`dt1`
    FOR EACH ROW BEGIN 
    INSERT INTO `db2`.`dt2`
    (ID,Fname)
  VALUES
    (NEW.ID,NEW.Fname);

    END$$

DELIMITER ;

////Update

USE db1;
DROP TRIGGER IF EXISTS t4;
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `t4` AFTER UPDATE
    ON `db1`.`dt1`
    FOR EACH ROW BEGIN
UPDATE `db2`.`dt2` 
    SET `dt2`.`Fname`=NEW.Fname WHERE `dt2`.`ID`=NEW.ID;
    END$$

DELIMITER ;

////Delete

USE db1;
DROP TRIGGER IF EXISTS t2;
DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `t2`  AFTER DELETE
    ON `db1`.`dt1`
    FOR EACH ROW BEGIN 
    DELETE FROM `db2`.`dt2` WHERE `dt2`.`ID`=OLD.ID;

    END$$
DELIMITER ;

Trigger on Multiple Mysql Database tables using php

2 Answers2