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

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

​x
 
CREATE TRIGGER Inventory_Update AFTER UPDATE ON AllInventoryFOR EACH ROWWHEN 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.

Answer

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

 
CREATE TRIGGER Inventory_Update AFTER UPDATE ON AllInventoryFOR EACH ROWWHEN 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​

Advertisement

Answer