Creating a Trigger in MySQL that has a JOIN

PROBLEM:
I'm trying to create a TRIGGER that will copy records from 2 different tables (users, addresses) in database A to a single table (users) in database B.

Basically, I'm trying to consolidate data into a single table in a different DB.

I have the following TRIGGER

CREATE TRIGGER `Copy db1_user to db2_users` 
AFTER INSERT ON `user`
FOR EACH ROW 
BEGIN 
    INSERT INTO database2.users(user_id, user_email, user_firstname, user_lastname) 
        VALUES (NEW.user_id, NEW.email, NEW.first_name, NEW.last_name);
END

The above works fine, where I bend my brain is getting the other chunk of data from the address table. I've created a diagram of what I'm trying to achieve, hopefully it makes sense. Oh, it also needs to make sure that the user_id doesn't already exist in the target table, which it shouldn't as the user_id field in the source table is AUTO INCREMENT.

My other question is, given the 2 source tables get written to at slightly different times, I'm not sure where to fire this TRIGGER? If I fire it on AFTER INSERT on table (users) will table (addresses) have been written to yet? Not sure - not my code (it's a WordPress application I'm drilling into), so any advice as to where to keep this TRIGGER would be great.

Suggestions or assistance would be greatly appreciated.

With 2 triggers you can solve the issue if your schemas are in the same server.

This can be achieved by using INSERT....ON DUPLICATE KEY UPDATE.

Trigger1

use database1;
CREATE TRIGGER after_users_insert AFTER INSERT ON users
 FOR EACH ROW
   INSERT INTO database2.users (user_id,user_email,user_firstname,user_lastname,user_address1,user_address2,user_city,user_state,user_zip,user_country,user_phone) 
   VALUES (NEW.user_id,NEW.email,NEW.first_name,NEW.last_name,null,null,null,null,null,null,null) 
       ON DUPLICATE KEY UPDATE  user_email     = NEW.email      ,
                                user_firstname = NEW.first_name ,
                                user_lastname  = NEW.last_name  ;

Trigger2

CREATE TRIGGER after_addresses_insert AFTER INSERT ON addresses
 FOR EACH ROW
   INSERT INTO database2.users (user_id,user_email,user_firstname,user_lastname,user_address1,user_address2,user_city,user_state,user_zip,user_country,user_phone) 
   VALUES (NEW.user_id,null,null,null,NEW.address_line_1,NEW.address_line_2,NEW.city,NEW.state,NEW.zip,NEW.country,NEW.phone) 
           ON DUPLICATE KEY UPDATE  user_address1 = NEW.address_line_1 ,
                                    user_address2 = NEW.address_line_2 ,
                                    user_city     = NEW.city           ,
                                    user_state    = NEW.state          ,
                                    user_zip      = NEW.zip            ,
                                    user_country  = NEW.country        ,
                                    user_phone    = NEW.phone          ;

See example

Note, I used null for the columns which are not present.

Another method is testing if user_id exist then update , otherwise make an insert with all the columns present in users table in database2.

Trigger1

use database1;

CREATE TRIGGER after_users_insert AFTER INSERT ON users
 FOR EACH ROW
  BEGIN
    IF EXISTS (SELECT user_id FROM  database2.users WHERE user_id = NEW.user_id )
     THEN 
       UPDATE database2.users SET user_email     = NEW.email     ,
                                  user_firstname = NEW.first_name,
                                  user_lastname  = NEW.last_name ;
      ELSE
         INSERT INTO database2.users (user_id,user_email,user_firstname,user_lastname,user_address1,user_address2,user_city,user_state,user_zip,user_country,user_phone) 
          VALUES (NEW.user_id,NEW.email,NEW.first_name,NEW.last_name,null,null,null,null,null,null,null);
    END IF;
  END;

Trigger2

CREATE TRIGGER after_addresses_insert AFTER INSERT ON addresses
 FOR EACH ROW
  BEGIN
   IF EXISTS ( SELECT user_id FROM  database2.users WHERE user_id = NEW.user_id )
     THEN 
         UPDATE database2.users SET user_address1 = NEW.address_line_1 ,
                                    user_address2 = NEW.address_line_2 ,
                                    user_city     = NEW.city           ,
                                    user_state    = NEW.state          ,
                                    user_zip      = NEW.zip            ,
                                    user_country  = NEW.country        ,
                                    user_phone    = NEW.phone          ;
     ELSE
         INSERT INTO database2.users (user_id,user_email,user_firstname,user_lastname,user_address1,user_address2,user_city,user_state,user_zip,user_country,user_phone) 
         VALUES (NEW.user_id,null,null,null,NEW.address_line_1,NEW.address_line_2,NEW.city,NEW.state,NEW.zip,NEW.country,NEW.phone) ;
   END IF;
  END;

See example

StackExchange Database Administrators Q#334895, answer score: 2