0

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....

user290687
  • 87
  • 2
  • 11

2 Answers2

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.

Anthony Williams
  • 66,628
  • 14
  • 133
  • 155
  • Hey hi, I am badly puzzled up in the TRIGGER operation of the sqlite. I am not able to understand how to use it. See I have created one trigger statement. and put it in the onCreate() of my database file. and also put one in the update, but what do i do in my code. how do I trigger my trigger code. How will it hit there. DO I need to do something else. – Shaista Naaz May 15 '11 at 18:21
  • If you create a trigger then it will run automatically when the appropriate events occur. If you have a further question I would suggest opening a new StackOverflow question to get complete answers. – Anthony Williams May 16 '11 at 16:04
  • I have posted 2 questions [Q.A](http://stackoverflow.com/questions/6010682/android-sqlite-trigger-operation-go-through) and [Q.B](http://stackoverflow.com/questions/5983137/how-to-use-trigger-in-android-sqlite) but unfortunately I am not getting any reply – Shaista Naaz May 16 '11 at 18:00
  • This trigger does not seem to account for Null values in ver column – gfrigon Jun 01 '16 at 17:16
0

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

RoboTamer
  • 3,474
  • 2
  • 39
  • 43