BEFORE INSERT trigger with primary key using the sqlite3 shell .import command-CodePudding

I'm trying to create a BEFORE INSERT trigger in Sqlite that catches my unique primary key column (UID) during import and replaces the remaining columns. I'm using sqlite3 command line for CSV import and whenever it sees an existing UID it doesn't update the other columns. This is the trigger I've played around with.

tldr: I want the other columns on a row to be replaced or updated during import with (UID) PRIMARY

CREATE TABLE "AllInventory" 
(
    "UID"   TEXT,
    "LotNumber" INTEGER,
    "ItemType"  TEXT,
    "AuctionDate"   TEXT,
    "AuctionHouse"  TEXT,
    "Location"  TEXT,
    PRIMARY KEY("UID")
)

CREATE TRIGGER BULK_Inventory_Update 
BEFORE INSERT ON AllInventory
FOR EACH ROW
WHEN EXISTS (SELECT * FROM AllInventory WHERE UID = New.UID)
BEGIN
    INSERT OR REPLACE INTO AllInventory(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 = new.LotNumber, ItemType = new.ItemType, AuctionDate = new.AuctionDate,
        AuctionHouse = new.AuctionHouse, Location = new.Location;
END

CodePudding user response：

You're on the right path here, but the trigger can be simplified a lot. Using a trimmed down example...

SQL:

CREATE TABLE example(UID TEXT PRIMARY KEY, blah1 TEXT, blah2 INTEGER);

CREATE TRIGGER bulk_update_example
BEFORE INSERT ON example
WHEN EXISTS (SELECT * FROM example WHERE UID = NEW.UID)
BEGIN
  UPDATE example
    SET (blah1, blah2) = (NEW.blah1, NEW.blah2)
    WHERE UID = NEW.UID;
  SELECT raise(IGNORE);
END;

The idea here is that when Sqlite tries to insert a row that has a UID that already exists in the table, it just updates that row and then silently cancels the insert that triggered the trigger, and the .import continues on with the next row. raise(IGNORE) doesn't roll back any changes to the database made in the trigger before it's called, so the update sticks.

Sample CSV file:

aa,aaa,1
bb,bbb,2
cc,ccc,3
aa,ddd,4
ee,eee,5

Sqlite3 shell session

(Table and trigger already present in the database):

sqlite> .import --csv foo.csv example
sqlite> SELECT * FROM example ORDER BY UID;
 ----- ------- ------- 
| UID | blah1 | blah2 |
 ----- ------- ------- 
| aa  | ddd   | 4     |
| bb  | bbb   | 2     |
| cc  | ccc   | 3     |
| ee  | eee   | 5     |
 ----- ------- -------

Note the aa row with the data from its second entry in the CSV file.