MySQL Trigger to insert data into different DB

Question

It sounds so simple... I spent a few hours just getting the first part working which was a mysql trigger to a different database. Now I want to get smart and JOIN a couple tables.

I have two master tables PROJ and COMP. Both share id. When PROJ gets inserted I want to insert some of the NEW.PROJ info and some of the COMP info into a single row in the db.table axis.axis_data

Would someone please help me do a SELECT...INSERT with a TRIGGER. I might be in over my head on this one

My WORKING Trigger.

DELIMITER $$

DROP TRIGGER IF EXISTS `rate_data_trigger` $$

CREATE TRIGGER rate_data_trigger
    BEFORE INSERT on PROJ FOR EACH ROW
    BEGIN

        INSERT INTO axis.axis_data
            (projinfo_table_id, rate_user, name,
             property_owner, property_address, property_city,
             property_state, property_zip, property_phone,
             rating_date, rating_type, rating_reason, rating_number
            )  

        VALUES
            (NEW.id, user(), NEW.BLGNAME,
             NEW.POWNER, NEW.STREET, NEW.CITY,
             NEW.STATE, NEW.ZIP, NEW.PHONE,
             NEW.RATDATE, NEW.RATTYPE, NEW.RATREAS, NEW.RATNGNO
            );
    END$$
DELIMITER ;

score 1 · Accepted Answer · answered Aug 19 '11 at 22:59

Simply use the following syntax in your select statement:

INSERT INTO axis.axis_data
        (projinfo_table_id, rate_user, name,
         property_owner, property_address, property_city,
         property_state, property_zip, property_phone,
         rating_date, rating_type, rating_reason, rating_number,
         field1, field2
        )  

    SELECT NEW.id, user(), NEW.BLGNAME,
         NEW.POWNER, NEW.STREET, NEW.CITY,
         NEW.STATE, NEW.ZIP, NEW.PHONE,
         NEW.RATDATE, NEW.RATTYPE, NEW.RATREAS, NEW.RATNGNO,
         c.field1, c.field2
    FROM COMP c WHERE c.id = NEW.id

If COMP doesn't always have a corresponding record in PROJ, you can do use SELECT ... FROM DUAL LEFT JOIN COMP c ON c.id = NEW.id

MySQL Trigger to insert data into different DB

1 Answers1

Linked