Using INSERT and UPSERT in SQLite trigger to update one table from another table?-CodePudding

I'm attempting to create a SQL database for an Auction company I work for. The issue I'm running into is creating triggers that update other inventory tables from the main inventory. I've tried quite a few different things, but can't wrap my head around how to get this to work. The latest trigger I've attempted is as follows:

CREATE TRIGGER Inventory_Update AFTER UPDATE ON AllInventory
FOR EACH ROW
WHEN new.Location LIKE 'Keyword'
BEGIN
    INSERT OR REPLACE INTO Inventory1 (UID, LotNumber, ItemType, AuctionDate, AuctionHouse, Location) 
    VALUES (new.UID, new.LotNumber, new.ItemType, new.AuctionDate, new.AuctionHouse, new.Location)
        ON CONFLICT (UID) DO 
           UPDATE SET LotNumber = excluded.LotNumber
                      AND ItemType = excluded.ItemType
                      AND AuctionDate = excluded.AuctionDate
                      AND AuctionHouse = excluded.AuctionHouse
                      AND Location = excluded.Location
           WHERE excluded.LotNumber = new.LotNumber
              OR excluded.ItemType = new.ItemType
              OR excluded.AuctionDate = new.AuctionDate
              OR excluded.AuctionHouse = new.AuctionHouse
              OR excluded.Location = new.Location;
END

Additional info: I have my main table AllInventory that will be updated regularly and needs to separate into 2 different tables. I have a UNIQUE constraint on all tables for the UID. This trigger I have any time the new.Location matches Inventory1.Location 'Keyword' it inserts new row into Inventory1 no problem, but if I update something in AllInventory that is already in Inventory1 with a UID it doesn't update the information I put in.

CodePudding user response：

I figured it out and was able to solve the above problem with:

CREATE TRIGGER Inventory_Update AFTER UPDATE ON AllInventory
FOR EACH ROW
WHEN new.Location LIKE 'Keyword'
BEGIN
    INSERT OR REPLACE INTO Inventory1 (UID, LotNumber, ItemType, AuctionDate, AuctionHouse, Location) 
    VALUES (new.UID, new.LotNumber, new.ItemType, new.AuctionDate, new.AuctionHouse, new.Location)
        ON CONFLICT (UID) DO 
            UPDATE 
            SET LotNumber = excluded.LotNumber, 
                ItemType = excluded.ItemType, 
                AuctionDate = excluded.AuctionDate, 
                AuctionHouse = excluded.AuctionHouse, 
                Location = excluded.Location;
END