SQLite Forum

Error Code : Error while executing SQL query on database ‘test’: no such column: price
Login
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
![image|549x160](upload://ba05X2OIOGgrfpZA7922lj186vk.png)

The Trigger 
```
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;
```
Test code to update the table 
```
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.