MySQL触发器将数据插入不同的数据库 [英] MySQL Trigger to insert data into different DB

问题描述

听起来很简单...我花了几个小时才开始让第一部分工作，这是另一个数据库的mysql触发器.现在，我想变得聪明起来，加入几张桌子.

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.

我有两个主表PROJ和COMP.两者共享id.当插入PROJ时，我想将一些NEW.PROJ信息和一些COMP信息插入db.table axis.axis_data

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

请有人帮我做一个 SELECT ... INSERT 和触发器".我可能会为此而烦恼

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

我的工作触发器.

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 ;

推荐答案

在select语句中仅使用以下语法:

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

如果COMP并不总是在PROJ中有相应的记录，则可以使用SELECT ... FROM DUAL LEFT JOIN COMP c ON 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触发器将数据插入不同的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！