Creating a Trigger in MySQL that has a JOIN

Question

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.

Is it required that the data in the database 2 be created immediately after a user is created in the database 1? — prasad_
– prasad_, Commented Jan 16, 2024 at 4:21
Provide SQL script which allows to reproduce your issue completely. Provide: CREATE TABLE for all tables; INSERT INTO with initial data (2-3 rows per table), INSERT queries and desired final data state after these INSERTs and according triggers have been executed (with detailed explanations for each inserted row and each altered value). — Akina
– Akina, Commented Jan 16, 2024 at 5:35
I'm not sure where to fire this TRIGGER? The logic tells: firstly the row in users is created, then the row in addresses which refers on the row in users is created. So the trigger on users must INSERT the row into the table in outer database, but the columns which matches the data from addresses is assigned with NULLs. And the trigger on addresses must UPDATE this row and set the address columns with according values. — Akina
– Akina, Commented Jan 16, 2024 at 5:40

Ergest Basha · Accepted Answer · 2024-01-17 11:20:01Z

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

INSERT .. ODKU seems to be more safe - it is solid SQL statement (so BEGIN-END not needed), it does not need in IF EXISTS, it is more clear, also it checks for any unique constraint. — Akina
– Akina, Commented Jan 16, 2024 at 9:32
Nice! I was considering having a trigger on the 'receiving' table in db2, so trigger 1 fired in the source db & triggered 2 fired after the source data had arrived in db2. But this is much neater. Thanks folks. Appreciate it. — Tony Stephens
– Tony Stephens, Commented Jan 18, 2024 at 7:08

Stack Exchange Network

Creating a Trigger in MySQL that has a JOIN

1 Answer 1

Your Answer

Hot Network Questions

Creating a Trigger in MySQL that has a JOIN

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions