CODEWITHSUNDEEP
sqlsqlitetriggersdatabase-trigger
Cowgirl
Cowgirl

Reputation: 97

How do I create an SQLite trigger on a table based on data in other tables?

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?

Upvotes: 0

Views: 1388

Answers (1)

shoek
shoek

Reputation: 400

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);

Upvotes: 1

Related Questions