[SQLite Tutorial] Bei INSERT die niedrigste "freie" ID nutzen

[Zvoni]

Mir war langweilig, und beim sörfen bin ich in einem Forum auf die Frage gestossen, wie man in SQLite bei einem INSERT die niedrigste "freie" ID nutzen kann.
Mir (und allen anderen) ist natürlich klar, dass sowas eigentlich nicht notwendig ist, aber ich fand die Herausforderung interessant.

Hier die Lösung: Wir brauchen einen BEFORE INSERT-Trigger.
Tabellen-, Spalten- und Triggernamen sollten entsprechend angepasst werden
ID ist ein Primärschlüssel INTEGER (egal ob AutoInc oder nicht)

CREATE TRIGGER trg_tbl_test2_before_insert BEFORE INSERT ON tbl_test2
FOR EACH ROW
/* Wird keine ID im INSERT mitgegeben, ist New.ID=-1 */
/* Und wir wollen ja nur dafür folgenden Code ausführen */
WHEN New.ID=-1  
BEGIN 
    INSERT INTO tbl_test2
    WITH
        /* Hole die "theoretisch" nächste ID ab */
        MaxID AS (SELECT Max(ID)+1 As NewMax FROM tbl_test2),
        /* Rekursives CTE, welches lückenlos alle ID's erzeugt zwischen 1 und MaxID.NewMax */
        CTE(ID) AS (SELECT 1 AS ID
                    UNION ALL
                    SELECT ID+1 FROM CTE INNER JOIN MaxID ON 1=1
                    WHERE ID+1<=MaxID.NewMax),
        /* Hier holen wir die niedrigste ID ab, welche nicht in der Tabelle existiert. LEFT JOIN mit Prüfung IS NULL im WHERE-Abschnitt */
        CT AS (SELECT Min(CTE.ID) As NewID FROM CTE LEFT JOIN tbl_test2 AS T ON CTE.ID=T.ID  WHERE T.ID IS NULL)     
    /* Hier "ersetzen" wir den Wert der neuen ID */
    /* Wichtig: Alle Spalten der Tabelle (Ausser ID) müssen im Format "New.SpaltenName" */
    /* angeführt werden, egal ob diese im Insert mitgegeben werden oder nicht */
    SELECT NewID, New.SomeText FROM CT; 
    /* Stoppe Ausführung des Triggers! WICHTIG! */ 
    SELECT RAISE(IGNORE);
END;

Fügt mal nen Ballen records in die Tabelle, dann löscht irgendwelche Records mittendrin, dann fügt neue Records hinzu (ohne jeweils im INSERT einen Wert für ID mitzuliefern)