CODEWITHSUNDEEP
pythonsqlsqlitetriggers
gavin
gavin

Reputation: 892

SQLite Trigger: Update a table after insert is done on another

I have three main tables to keep track of products, location and the logistics between them which includes moving products to and from various locations. I have made another table balance to keep a final balance of the quantity of each product in respective locations.

Here are the schemas:

products(prod_id INTEGER PRIMARY KEY AUTOINCREMENT,
         prod_name TEXT UNIQUE NOT NULL,
         prod_quantity INTEGER NOT NULL,
         unallocated_quantity INTEGER)

Initially, when products are added, prod_quantity and unallocated_quantity have the same values. unallocated_quantity is then subtracted from, each time a certain quantity of the respective product is allocated.

location(loc_id INTEGER PRIMARY KEY AUTOINCREMENT,
         loc_name TEXT UNIQUE NOT NULL)

logistics(trans_id INTEGER PRIMARY KEY AUTOINCREMENT,
          prod_id INTEGER NOT NULL,
          from_loc_id INTEGER NULL,
          to_loc_id INTEGER NOT NULL,
          prod_quantity INTEGER NOT NULL,
          trans_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY(prod_id) REFERENCES products(prod_id),
          FOREIGN KEY(from_loc_id) REFERENCES location(loc_id),
          FOREIGN KEY(to_loc_id) REFERENCES location(loc_id))

balance(prod_id INTEGER NOT NULL,
        loc_id INTEGER NOT NULL,
        quantity INTEGER NOT NULL,
        FOREIGN KEY(prod_id) REFERENCES products(prod_id),
        FOREIGN KEY(loc_id) REFERENCES location(loc_id))

At each entry made in logistics, I want a trigger to update the values in balance thereby keeping a summary of all the transactions (moving products between locations)

I thought of a trigger solution which checks if for each insert on the table logistics, there already exists the same prod_id, loc_id entry in the balance table, which if exists will be updated appropriately. However, I don't have the experience in SQLite to implement this idea.

Upvotes: 0

Views: 2032

Answers (1)

MikeT
MikeT

Reputation: 56953

I believe that your TRIGGER would be along the lines of either :-

CREATE TRIGGER IF NOT EXISTS logistics_added AFTER INSERT ON logistics
BEGIN
    UPDATE balance SET quantity = ((SELECT quantity FROM balance WHERE prod_id = new.prod_id AND loc_id = new.from_loc_id) - new.prod_quantity) WHERE prod_id = new.prod_id AND loc_id = new.from_loc_id;
        UPDATE balance SET quantity = ((SELECT quantity FROM balance WHERE prod_id = new.prod_id AND loc_id = new.to_loc_id) + new.prod_quantity) WHERE prod_id = new.prod_id AND loc_id = new.to_loc_id;
END;

or :-

CREATE TRIGGER IF NOT EXISTS logistics_added AFTER INSERT ON logistics
BEGIN
    INSERT OR REPLACE INTO balance VALUES(new.prod_id,new.from_loc_id,(SELECT quantity FROM balance WHERE prod_id = new.prod_id AND loc_id = new.from_loc_id) - new.prod_quantity);
        INSERT OR REPLACE INTO balance VALUES(new.prod_id,new.to_loc_id,(SELECT quantity FROM balance WHERE prod_id = new.prod_id AND loc_id = new.to_loc_id) + new.prod_quantity);
END;

Note that the second relies upon adding a UNIQUE constraint to the balance table by using PRIMARY KEY (prod_id,loc_id) or alternately UNIQUE (prod_id,loc_id). The UNIQUE constraint would probably be required/wanted anyway.

The subtle difference is that the second would INSERT a balance row if and appropriate one didn't exist. The latter would do nothing if the appropriate balance row didn't exist.

Upvotes: 1

Related Questions