mysql - 创建一个触发器，从 phpMyAdmin 中的另一个表中提取信息

Question

我正在尝试创建一个相当复杂的触发器，但我不确定是否可以在 phpMyAdmin 上完成。

现在我有一个查询，它创建一个表，其中包含我需要的所有信息。

CREATE TABLE SeniorDB_Shipping

SELECT

    SeniorDB_Invoice.ID_Order, 
    SeniorDB_Customer.MCT_Code,
    SeniorDB_Customer.Customer_Name,
    SeniorDB_Customer.Customer_Address,
    SeniorDB_Customer.Customer_City,
    SeniorDB_Customer.Customer_State,
    SeniorDB_Invoice.Shipping_Company

FROM SeniorDB_Customer

Join SeniorDB_Invoice ON SeniorDB_Customer.MCT_Code = SeniorDB_Invoice.MCT_Code

正如您在图片中看到的，当我运行查询时，它会从信息上方的表中提取信息。我正在尝试(但失败了)创建一个触发器来执行相同的操作，而不必每次都创建一个全新的表。我看到的所有其他帖子都与创建表类似，而不是插入到表。

触发器的作用是，当我输入ID_Order时，其余信息将从数据库中获取。

这是我到目前为止的触发器:

    delimiter ~

        create trigger SeniorDB_Shipping before insert on SeniorDB_Shipping
        for each row begin
            set new.SeniorDB_Shipping.MCT_Code =            new.SeniorDB_Customer.MCT_Code,;
            set new.SeniorDB_Shipping.Customer_Name =       new.SeniorDB_Customer.Customer_Name,;
            set new.SeniorDB_Shipping.Customer_Address =    new.SeniorDB_Customer.Customer_Address,;
            set new.SeniorDB_Shipping.Customer_City =       new.SeniorDB_Customer.Customer_City,;
            set new.SeniorDB_Shipping.Customer_State =      new.SeniorDB_Customer.Customer_State,;
            set new.SeniorDB_Shipping.Shipping_Company =    new.SeniorDB_Customer.Shipping_Company,;
        end~

    delimiter ;

我感觉我就在那里。当我输入 ID_Order 时，我无法链接到它。

如果您想查看数据库，请访问此页面:http://polonium.forest.usf.edu/~sngamwon/SeniorProject/SeniorDB_Order.php

Answer 1

好的，所以您需要运行一次:

/* Create the table with a primary key */
create table `SeniorDB_Shipping` (
    `id` INT unsigned AUTO_INCREMENT NOT NULL,
    primary key(id),
    `ID_Order` int NOT NULL,
    `MCT_Code` int NOT NULL,
    `Customer_Name` varchar(255) NOT NULL,
    `Customer_Address` varchar(255) NOT NULL,
    `Customer_City` varchar(255) NOT NULL,
    `Customer_State` varchar(255) NOT NULL,
    `Shipping_Company` varchar(255) NOT NULL
) ENGINE=MyISAM CHARACTER SET=utf8 COLLATE utf8_general_ci;

然后就可以运行了

/* Insert statement */
INSERT INTO `SeniorDB_Shipping` (
    `ID_Order`, 
    `MCT_Code`, 
    `Customer_Name`, 
    `Customer_Address`, 
    `Customer_City`,
    `Customer_State`,
    `Shipping_Company`
) SELECT
    invoice.ID_Order, 
    customer.MCT_Code,
    customer.Customer_Name,
    customer.Customer_Address,
    customer.Customer_City,
    customer.Customer_State,
    invoice.Shipping_Company
FROM 
    SeniorDB_Customer as customer
    Join SeniorDB_Invoice as invoice
        ON customer.MCT_Code = invoice.MCT_Code;

我已经在我自己的 PHPMyAdmin 中运行了这个，所以它可以工作。但我显然没有你的架构。已知问题:

• 这将每次使用两个表中的全部数据填充 SeniorDB_Shipping。如果这不是您想要的，请根据需要修改查询以仅选择最近的数据。如果 ID_Order 是主键，您可以检查它是否已经存在。