The ORM is inside me.

gdivelog new dive trigger

DROP TRIGGER Dive_Insert;

/*
   if no dives predate this then
      if lowest number is > 1:
         lowest number - 1
      else 
         1
   else
       pick most recent +1
*/
CREATE TRIGGER Dive_Insert AFTER INSERT ON Dive 
BEGIN
    UPDATE Dive
    SET dive_number=
        (SELECT CASE WHEN
            /* If there are dives with datetime before this, ie. we're inserting 
               dives before any others ones in the log.
            */
            (SELECT dive_number
             FROM Dive                  
             WHERE dive_datetime < NEW.dive_datetime
               AND dive_id != NEW.dive_id
             ORDER BY dive_datetime DESC LIMIT 1) IS NULL THEN
                 (SELECT CASE WHEN
                     /* Check if the lowest dive number is greater than 1. 
                        "Greater than" to prevent inserting 0 or negative. 
                     */
                     (SELECT MIN(dive_number)
                      FROM Dive
                      WHERE dive_number > 0) > 1 THEN
                          /* if so, select that number minus 1 */
                          (SELECT MIN(dive_number)-1 FROM Dive WHERE dive_number > 1) 
                      ELSE 
                          /* Else start at 1 */
                          1
                      END)
             ELSE
                 /* Common case, pick highest number sorted by datetime, plus 1 */
                 (SELECT MAX(dive_number)+1
                  FROM Dive
                  WHERE dive_datetime <= NEW.dive_datetime
                    AND dive_id != NEW.dive_id
                    AND dive_number > 0
                  ORDER BY dive_datetime DESC LIMIT 1)
             END)
    WHERE dive_id=NEW.dive_id;
END;


INSERT INTO Dive (dive_datetime, dive_duration, dive_maxdepth, dive_mintemp, dive_maxtemp, dive_notes, site_id, dive_visibility, dive_weight) VALUES ('2012-03-19 12:11:00', 60, 56.0, 8.7, 10.7, 'hello dive', 1, 10.0, 12.0);

SELECT dive_number FROM dive WHERE dive_datetime = '2012-03-19 12:11:00';