mysql - 插入子表时触发将值插入父表

Question

我有两个表，结构如下。当我尝试添加到 books 表时出现错误，我无法添加或更新我期望的子行。我希望在添加到 books 表之前，可以创建一个触发器来将出版商名称添加到 publisher 表。

这是我第一次使用触发器，我不确定自己做错了什么，也找不到示例。

create table publishers
    (name varchar(25) not null, 
    address varchar(55), 
    phone varchar(12),
    PRIMARY KEY(name)
    ) ENGINE = INNODB;

create table books
    (book_id int(4) not null auto_increment, 
    title varchar(40), 
    publisher_name varchar(25),
    primary key (book_id),
    foreign key (publisher_name) references publishers(name) ON UPDATE CASCADE ON DELETE CASCADE 
    ) ENGINE=INNODB;

以及我要创建的触发器:

DELIMITER ///
CREATE TRIGGER add_publisher BEFORE insert ON books
    FOR EACH ROW
    BEGIN
    INSERT INTO publishers(name) values (books.pubisher_name);
END;
///

DELIMITER ;

Answer 1

将 books.publisher_name 替换为 NEW.publisher_name。

    DELIMITER ///
    CREATE TRIGGER add_publisher BEFORE insert ON books
        FOR EACH ROW
        BEGIN
        INSERT INTO publishers(name) values (NEW.publisher_name);
    END;
    ///
DELIMITER ;