3

I have database_1 and database_2 both on the same server.

How can I instantly update the USER table in database_2 when data is inserted into the USER table in database_1?

Structure of both tables:

CREATE TABLE USER 
(
    id int
    , email varchar(256)
    , `name` varchar(1000)
    , `address` varchar(1000)
);

Please help me with all steps to do this.

3

You can probably use a trigger to accomplish your goal.

Consider the following simplified example:

CREATE TABLE t1
(
    c1 int
    , c2 int
);

CREATE TABLE t2
(
    c1 int
    , c2 int
);

CREATE TRIGGER t1_trigger AFTER INSERT ON t1
FOR EACH ROW
BEGIN
    INSERT INTO t2 (c1, c2)
    VALUES (New.c1, New.c2);
END;

INSERT INTO t1 (c1, c2)
VALUES(1, 2);

Check the results:

SELECT *
FROM t2;
╔═════╦════╗
║ c1  ║ c2 ║
╠═════╬════╣
║  1  ║  2 ║
╚═════╩════╝

I've created a simple fiddle that shows the working code, and allows you to play with it.

The above creates two tables, the first with a trigger that copies rows inserted into t1 into t2. In the above example, both tables are in the same database... since you are attempting to copy from one database to another, the following syntax might work in the trigger if both databases are on the same MySQL Server:

CREATE TRIGGER t1_trigger AFTER INSERT ON t1
FOR EACH ROW
BEGIN
    INSERT INTO other_database.t2 (c1, c2)
    VALUES (New.c1, New.c2);
END;
0

Generally, it is bad design to have the same data in two places.

Rethink the requirement. See if you can store the user only one place. Note that you can reference multiple databases in the same query via this syntax:

 db1.table_a

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.