CREATE TABLE wishlist(
year YEAR NOT NULL,
ssn CHAR(14),
delivered BIT,
PRIMARY KEY (year, ssn),
FOREIGN KEY (ssn) REFERENCES child (child_ssn)
) engine=innodb;

CREATE TABLE wishlistDelivered(
year YEAR NOT NULL,
ssn CHAR(14),
delivered BIT,
PRIMARY KEY (year, ssn),
FOREIGN KEY (ssn) REFERENCES child (child_ssn)
) engine=innodb;

DELIMITER //

    CREATE TRIGGER triggerdelivered after insert on wishlist
    FOR EACH ROW    BEGIN 
        INSERT INTO wishlistDelivered
            SELECT * FROM wishlist WHERE delivered = 1;
        DELETE FROM wishlist WHERE delivered = 1;
    END;
//
DELIMITER ;

我本质上是在试图创建一个触发器，它可以将所有已被送到冷库的愿望清单上的物品移动。如果玩具已交付，则愿望列表中的Delivered列中的值为“1”。但是，我仍然可以在值为“1”的地方插入数据，并且它不会更新wishlistDelivered表，也不会从wishlist表中删除数据。我遗漏了什么？