Q:

SQLite trigger Issue

I have a trigger on a table which gets triggered on any new insert...Here is the code for the trigger. This trigger has to calculate duration in seconds and insert the record in the table.
CREATE TRIGGER duration_trigger 
BEFORE INSERT on StudentData 
BEGIN 
UPDATE StudentData set duration = (select cast( (julianday(enddatetime) - julianday(startdatetime) ) *24 * 60 *60 as integer) from StudentData); 
END

In StudentsData table Duration column is defined as INTEGER,
StartDatetime and EndDatetime are defined as TEXT
Here comes my issue.
Trigger gets triggered, but the value in Duration column is always 7
When I execute the same select query that is in the the trigger in a SQL tool, it gives me correct duration in seconds. Trigger on the database is not producing the same result...what could be the issue?
I am also attaching screenshots of the trigger data in the table and select query results from same table.
Table results after trigger.

Select Query results

A:

Basically you are updating all rows as you are not specifying a WHERE clause for the update. So the very last successful update will apply the value to all rows, hence why they are all 7.
Furthermore before you have inserted a row what is there to update? I don't think this can be done an analogy would be; Before you build the wall paint the wall.
Now you could UPDATE after the insert, but care needs to be taken when using UPDATE i.e. if you want to update anything other than all rows then you need to restrict the update to the required rows. A WHERE clause can do this.
As such if you were to ensure that an inserted column were set to an invalid value (as far as your view of the data e.g. a duration of -1 would only suit Dr. Who (apologies to any other Time Travellers)).
Null could also be used.
However, I prefer using a value that is specifically set. Assuming that the row is inserted with duration being given a value of -1 (e.g. duartion INTEGER DEFAULT -1) Then :-
CREATE TRIGGER duration_trigger001
AFTER INSERT on StudentData 
BEGIN 
        UPDATE StudentData SET duration =  ((julianday(enddatetime) - julianday(startdatetime)) * 24 * 60 * 60) WHERE duration = -1;
END;

Would work e.g. :-

Notes

The first two rows were added before the trigger was created.
Row 10 was deleted because I used . instead of : as a separator it did nothing.
I didn't cast to INT for simplicity/laziness.

