Databases
StackOverflow2013
Postsdbo
POSQLite Trigger issue - "No such column"
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLite Trigger issue - "No such column"
    text
    copied!<p>I have a sqlite database with a table named Achievements, it stores whether certain achievements have been met in a simple quiz I am building (iphone project to learn objective-c). The table structure is:</p> <pre><code>ID Name Completed ============================= 1 FiveInARow 0 2 TenInARow 0 3 AllCorrect 0 4 UnderASecond 0 5 AllCompleted 0 </code></pre> <p>The ID is the primary key, Name is a VARCHAR and Completed is BOOL (0 for false and 1 for true).</p> <p>I am trying to add a trigger onto the update statements of this table, such that when a Completed column is set to 1 (i.e. achievement completed - this is the only update that will happen on the table) there will be a calculation to see whether all of the other achievements have been completed and if so also update the AllCompleted achievement (ID 5) to 1.</p> <p>The trigger I created is:<br> <code>CREATE TRIGGER "checkAllAchievements" AFTER UPDATE ON "Achievements"<br> WHEN (Select SUM(Completed) from Achievements) = (Select COUNT(Completed) -1 from Achievements)<br> BEGIN UPDATE Achievements SET Completed = 1 WHERE Name= 'AllCompleted';</code></p> <p>So what I am trying to do is take the sum of the completed row and compare it to the total count minus 1, if they match then it means that all achievements apart from the AllCompleted achievement are achieved so we can also set that to true. The trigger gets created fine.</p> <p>Now on to the problem - When I attempt to update the table with the following statement </p> <p><code>UPDATE Achievements SET Completed = 1 WHERE ID = 1</code></p> <p>I receive the error message <strong>"No such Column Completed"</strong>.</p> <p>Am I trying to do something that is fundamentally wrong? Is it not possible for me to achieve this using a trigger? Any advice?</p> <p>Thanks.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload