SQLite Forum

Triggers
Login

Triggers

(1) By anonymous on 2020-10-13 06:48:18 [link] [source]

I Need to create a Trigger which will insert the odometer from the Vehicle table into the Rental Table when the below insert statement is run.

INSERT INTO rental (customerId, VIN, odo_out, odo_back, date_out, date_back) VALUES(1, 'A0X2Z', 200, NULL, '2020-09-24',NULL);

Schemas:

CREATE TABLE Vehicle( carMake TEXT NOT NULL, carModel TEXT NOT NULL, carYear INTEGER NOT NULL, VIN TEXT NOT NULL, odometer INTEGER NOT NULL, PRIMARY KEY (VIN) CHECK (length(VIN) == 5) CHECK (UPPER(VIN) == VIN) CHECK (SUBSTR(VIN,3,1) =='X' OR SUBSTR(VIN,3,1) == '0' OR SUBSTR(VIN,3,1) == '1' OR SUBSTR(VIN,3,1) == '2' OR SUBSTR(VIN,3,1) == '3' OR SUBSTR(VIN,3,1) == '4' OR SUBSTR(VIN,3,1) == '5' OR SUBSTR(VIN,3,1) == '6' OR SUBSTR(VIN,3,1) == '7' OR SUBSTR(VIN,3,1) == '8' OR SUBSTR(VIN,3,1) == '9') CHECK (VIN NOT LIKE '%I%' OR VIN NOT LIKE '%O%' OR VIN NOT LIKE '%Q%') );

CREATE TABLE Rental( customerID INTEGER NOT NULL, VIN TEXT NOT NULL, odo_out INTEGER, odo_back INTEGER NULL, date_out TEXT, date_back TEXT NULL, FOREIGN KEY (customerID) REFERENCES Customer(id) ON UPDATE CASCADE FOREIGN KEY (VIN) REFERENCES Vehicle(VIN) ON UPDATE CASCADE );

(2) By anonymous on 2020-10-13 08:26:20 in reply to 1 [link] [source]

Hi To,

Is it an idea to abbreviate this:

CHECK (SUBSTR(VIN,3,1) =='X' OR SUBSTR(VIN,3,1) == '0' OR SUBSTR(VIN,3,1) == '1' OR SUBSTR(VIN,3,1) == '2' OR SUBSTR(VIN,3,1) == '3' OR SUBSTR(VIN,3,1) == '4' OR SUBSTR(VIN,3,1) == '5' OR SUBSTR(VIN,3,1) == '6' OR SUBSTR(VIN,3,1) == '7' OR SUBSTR(VIN,3,1) == '8' OR SUBSTR(VIN,3,1) == '9')

to CHECK (SUBSTR(VIN,3,1) IN (X', '0', '1', '2', '3', '4', '5', '6','7'.0 '8','9')

which is more readable but not more efficient!

Although, I think that regular expressions will do a better job in these cases.

(3) By Ryan Smith (cuz) on 2020-10-13 10:30:21 in reply to 1 [source]

Adding a Trigger to do something, almost anything really, upon inserting (or update or delete etc.) is trivial. This documentation should get you there easily.

Contrary to the Anonymous poster's assertion, I believe their proposed CHECK constraint is indeed many times more efficient, if for no other reason than running the "SUBSTR()" function many times less.

Another side-note - your other CHECK constraint, namely:

  CHECK (VIN NOT LIKE '%I%' OR VIN NOT LIKE '%O%' OR VIN NOT LIKE '%Q%')  
is almost guaranteed to not be correct. It will succeed on ANY combination of those Q, O and I characters, except in the explicit case when they are ALL 3 present in the same VIN. This could be the intent, but seems unlikely. Did you perhaps mean:
  CHECK (VIN NOT LIKE '%I%' AND VIN NOT LIKE '%O%' AND VIN NOT LIKE '%Q%')  
which changes the meaning to "Do not allow entries containing any 'Q' or 'O' or 'I'." - which feels to me like the intent - but I could of course be wrong.

Good luck!