Error Code : Error while executing SQL query on database ‘test’: no such column: price
(1.2) By monkelvin (kelvimchop) on 2021-10-15 10:07:30 edited from 1.1 [link] [source]
SQLite question (Sorry if I choose the wrong topic category ) I am creating a trigger for my SQLite code, which it uses to update the field rental_cost (inside table Rental, default NULL) if someone UPDATE or INSERT the related table, the rental_cost is a REAL field that requires calculation and data from another table so I use JOIN to grip data from other tables, however, it shows the above error code when I tried to test it.
Rental table structure
Test code to update the table
CREATE TRIGGER updater AFTER UPDATE ON Rental FOR EACH ROW WHEN OLD.rental_cost = NULL AND NEW.date_back != NULL BEGIN SELECT price FROM Rental JOIN License USING ( license_id ) JOIN Title USING ( title ); UPDATE Rental SET rental_cost = 3 + price * 0.05 * (date_out) - (date_back) WHERE rental_cost = NULL; END;
UPDATE Rental SET date_back = '0000-00-11 11:11:11' WHERE gamer_id = '12' AND license_id = '71811';
Expected outcome: the data_back update manually, and trigger detect the update, calculate and update the rental_cost automatically.
Edited 1.0 : The SQLiteStudio empty field IS NOT NULL, when I use ` SELECT rental_cost FROM gameRental WHERE rental_cost IS NOT NULL the last field which is empty by default, show up in the query result. Vice versa, when IS NULL, the query result show nothing
Edited 2.0 : This is weird. When I completely delete the table and recreate it, use SQL edit instead of the provided Triggers Function from the SQLITeStudio Tools bar, and type all the code myself, the trigger works.
(2) By Stephan Beal (stephan) on 2021-10-15 08:51:02 in reply to 1.0 [link] [source]
WHEN OLD.rental_cost = NULL AND NEW.date_back != NULL
NULL will never compare properly that way. Use
IS NULL and
IS NOT NULL to compare against NULL values.
(5.1) By monkelvin (kelvimchop) on 2021-10-15 09:00:06 edited from 5.0 in reply to 2 [link] [source]
thank for your reply, I manage to change the subquery inside the update statement ,
CREATE TRIGGER updater AFTER UPDATE ON Rental FOR EACH ROW WHEN OLD.rental_cost IS NULL AND NEW.date_back IS NOT NULL BEGIN UPDATE Rental SET rental_cost = 3 + ( SELECT price FROM Rental JOIN License USING ( license_id ) JOIN Title USING ( title ) ) * 0.05 * (date_out) - (date_back) WHERE rental_cost = NULL; END;
however, the new problem is the trigger not detecting the UPDATE statement and not fire. I believe is something wrong with my WHEN statement
(3) By Tim Streater (Clothears) on 2021-10-15 08:51:53 in reply to 1.0 [link] [source]
The table structure image is not visible.
(4.1) By monkelvin (kelvimchop) on 2021-10-15 08:57:20 edited from 4.0 in reply to 1.0 [link] [source]
Sorry here is the table structure from sqlitestudio
(6.1) By Gunter Hick (gunter_hick) on 2021-10-15 09:04:06 edited from 6.0 in reply to 1.0 [link] [source]
Lots of misconceptions here. x) SQLite trigger programs do not have variables. The result column price from the SELECT ist not available to the separate UPDATE statement. Use a subselect to retrieve the price that relates to the row being updated. x) The UPDATE is going to affect every row of Rental that matches the WHERE clause, not just the NEW row. Use rowid = NEW.rowid instead x) rental_cost = NULL is always FALSE; nothing is ever equal to NULL, not even NULL. Use IS NULL to check for NULL values. x) I think you meant to compute (date_out - date_back), not subtract a date from a product of a price and a date. Edit: Subtracting dates stored as TEXT will probably not yield the expected result.
(7.2) By monkelvin (kelvimchop) on 2021-10-15 09:27:37 edited from 7.1 in reply to 6.1 [link] [source]
Thanks so much for your clarification, it means a lot.
Would you please explain the subselect? And,
The date_back and rental_cost is default to be NULL, date_back will change until someone update new datetime string,and the rental_cost will be update by the trigger because of someone UPDATE the table.
The calculation of the rental cost is using the following formula
rental cost = 3 + price × 0.05 × days rented
which the days rented is done by subtraction of two rows date_out - date_back date_out & date_back is two TEXT datatype with YYYY-MM-DD HH:MM: SS string format
My friend suggested I use julianday() to do it
And here is my edited version
CREATE TRIGGER updater
FOR EACH ROW
WHEN OLD.rental_cost IS NULL AND
NEW.date_back IS NOT NULL
SET rental_cost = 3 + (
License USING (
Title USING (
* 0.05 * (date_out) - (date_back)
WHERE rental_cost IS NULL;
Once again, thanks so much for your help
(8) By Gunter Hick (gunter_hick) on 2021-10-15 10:24:58 in reply to 7.2 [source]
This will probably calculate the number of days: (julianday(date_out) - julianday(date_back) + 1) You need to use WHERE rowid = NEW.ROWID to restrict changes to the affected row. WHERE rental_cost IS NULL will update ALL of the rows that have a NULL rental cost The price in the subselect will be the one from the first Rental record visited in the subselect. I don't think that is what you intend. If you want the price from the affected row, just use NEW.price instead.
(9) By monkelvin (kelvimchop) on 2021-10-15 11:16:49 in reply to 8 [link] [source]
Thank you so much, that's very clear! Wish you a nice day!