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

Question

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

Also i don't understand your question, why you want a trigger your insert if you use upsert see https://www.sqlite.org/lang_UPSERT.html and uid is the primary key, it will update all the rows you psecify — nbk, Jul 15 '22 at 20:05
@nbk Sorry for the confusion, what I’m attempting to have happen is as follows: Say AllInventory has a row containing L150, 150, Furniture, July 2022, Auction, Auction (L150 is UID primary key) When I import a csv into AllInventory that contains (L150, 200, Furniture, July 2022, Auction, Auction2) and (L151, 151, Furniture, July 2022, Auction, Auction2). I want it to update L150 with new data and also insert new L151. — DonShaun, Jul 15 '22 at 20:18

score 1 · Accepted Answer · answered Jul 15 '22 at 23:35

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.

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

1 Answers1

SQL:

Sample CSV file:

Sqlite3 shell session