0

I have two tables - 

part(partID, brand, price, size)

consists_of(customerID, partID, quantity, price, shipdate)
                        |
                        FK part.ID from part

Table part is never going to be changed/updated, but consists_of will be.

  1. How do I add a [before insert?] trigger on consists_of that checks if consists_of.price for each entry is less than or equal consists_of.quantity * part.price for that particular consists_of.partID and raises an abort if it isn't so?

Or,

  1. How do I add [after insert?] trigger on consists_of that does INSERT INTO consists_of(price) VALUES (...) where the value of consists_of.price is equal to consists_of.quantity * part.price for that consists_of.partID?
Cowgirl
  • 97
  • 5
  • Have you tried function and trigger? https://stackoverflow.com/a/24607012/12656244 Or is your matter more complicated? – shoek May 19 '20 at 09:52
  • @shoek 1. That post is similar but doesn't help a lot. SQLite doesn't support variables which I think are needed here. 2. https://stackoverflow.com/questions/15618271/mysql-how-do-i-create-a-trigger-that-calculates-a-field-value-based-on-value-fr?rq=1 talks about something very similar to my question, but again, no variable support here. – Cowgirl May 19 '20 at 10:01
  • I'll give you an answer without variables later... AFAIK, there's no straightforward way to use variables in SQLite. Temporary table is an alternative, but is not applicable here. We can use variables in a user-defined function, but there's no `CREATE FUNCTION` in SQLite: we need to call C API or PHP/Python/... wrapper to create functions. https://stackoverflow.com/a/14574227/12656244 https://stackoverflow.com/a/24272361/12656244 https://stackoverflow.com/a/2108921/12656244 – shoek May 19 '20 at 12:14

1 Answers1

1

If I understand you correctly, you can select part.price in subqueries and calculate part.price * consists_of.quantitiy.

  1. before insert
CREATE TABLE part(part_id INTEGER, price INTEGER);
CREATE TABLE consists_of(customer_id INTEGER, part_id INTEGER, quantity INTEGER, price INTEGER);

INSERT INTO part VALUES(10, 50);
INSERT INTO part VALUES (20, 1000);

CREATE TRIGGER IF NOT EXISTS raise_if_consists_of_price_too_expensive
AFTER INSERT ON consists_of
WHEN new.price > (SELECT part.price * new.quantity FROM part WHERE part.part_id = new.part_id)
BEGIN
  SELECT RAISE (ABORT, 'too expensive.');
END;

 -- OK
INSERT INTO consists_of(customer_id, part_id, quantity, price)
VALUES(10050, 20, 31, 100);
 -- OK
INSERT INTO consists_of(customer_id, part_id, quantity, price)
VALUES(80030, 10, 9, 50 * 9);
 -- Raise abort
INSERT INTO consists_of(customer_id, part_id, quantity, price)
VALUES(80099, 10, 9, 50 * 9 + 1);
  1. after insert
CREATE TABLE part(part_id INTEGER, price INTEGER);
CREATE TABLE consists_of(customer_id INTEGER, part_id INTEGER, quantity INTEGER, price INTEGER);

INSERT INTO part VALUES(10, 50);
INSERT INTO part VALUES (20, 1000);

CREATE TRIGGER IF NOT EXISTS fill_consists_of_price
AFTER INSERT ON consists_of
BEGIN
  UPDATE consists_of
  SET
  price = (
    SELECT consists_of.quantity * part.price
    FROM part
    WHERE part.part_id = consists_of.part_id
  )
  WHERE customer_id = new.customer_id AND part_id = new.part_id
  ;
END;

INSERT INTO consists_of(customer_id, part_id ,quantity)
VALUES(10050, 20, 31);

INSERT INTO consists_of(customer_id, part_id ,quantity)
VALUES(80033, 10, 9);
shoek
  • 380
  • 2
  • 9
  • This is spot on precise. Thank you. I didn't know it could be done without variables. Where do I find more examples like these, or say a resource that I can read up on? – Cowgirl May 21 '20 at 06:53
  • 1
    Sorry, I'm not experienced in SQLite. If you want a learning material, it would be better for you to find someone else. I hope you find a good mentor. FYI, what I did here was: 1. Googled "sqlite trigger insert another table" and found https://stackoverflow.com/a/49604621/12656244 2. Googled "sqlite trigger if clause", "sqlite trigger abort" and found https://stackoverflow.com/a/4609225/12656244 https://stackoverflow.com/q/16906172/12656244 3. Combined the results. 4. Tried on my computer. – shoek May 21 '20 at 11:27