Trigger to move table data when updated-CodePudding

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 ;

I'm essentially trying to create a trigger where it moves all wishlist items that's been delivered to a cold storage. If a toy is delivered, the value is "1" in the delivered column in the wishlist table. However, I can still insert data where the value is "1" and it does not update the wishlistDelivered table nor remove the data from the wishlist table. What am I missing?

CodePudding user response：

A trigger can’t fire update or insert statements into a table that invoked the trigger action. What you can do is to manipulate update or insert statements within the trigger code (BEFORE triggers)

CodePudding user response：

This does not logically make sense to me. I would expect the wishlist table to exist in 2 states (at least) where the table is first INSERTed to with delivered = false the UPDATEd when delivered setting delivered to true. However your code is an insert trigger..

Triggers operate FOR EACH ROW so selecting all rows from wishlist for insert to wishlistdelivered is just wrong you should be inserting NEW. values (see manual)

But the killer here is that you cannot action the table which fired the trigger ie the DELETE is not allowed.

If it was me I would create a scheduled EVENT containing the code you have in your trigger rather than a trigger.