SQLite Trigger to Autoincrement one column when another is not unique

I am looking for a way to have a trigger that looks at a Timestamp fi开发者_运维知识库eld 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....

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.

I voted Anthony Williams up but after some testing I found that it doesn't work right.

I did some more reading and actually, you can create an alias of SQLite's internal ROWID by just adding INTEGER PRIMARY KEY to your id, or just use ROWID and don't bother adding your own field.

More about this: http://www.sqlite.org/autoinc.html