SQLite Trigger to Autoincrement one column when another is not unique

Question

I am looking for a way to have a trigger that looks at a Timestamp field and if it finds a match autoincrement the record being inserted on another column. So I would have a timestamp field and a version field. If a record being inserted has a timestamp that matches a record already in the table then autoincrement the version field. Any ideas....

Answer 1

Assuming your version column is ver and your timestamp column is ts

CREATE TRIGGER foo AFTER INSERT ON sometable
BEGIN
    UPDATE sometable SET
        ver=(SELECT MAX(ver)+1 FROM sometable WHERE ts=NEW.ts)
    WHERE rowid=NEW.rowid;
END;

If you specify a default value of 0 for your ver column then the entries will be numbered 1 onwards.