Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to create an unique auto increment index field that is re-generated on both inserts and updates. I need it so that a client can send a server an integer and get back all outdated (created or updated) records. Something that would allow:

Insert Record A (Record A 'touched' set to '1')  
Insert Record B (Record B 'touched' set to '2')
Update Record A (Record A 'touched' set to '3')
Update Record B (Record B 'touched' set to '4')

I'm using both Postgresql and Sqlite (production and development) and would ideally like a solution that will work under both. What is the best way to add something like this?

Thanks!

share|improve this question
    
a) I'm curious about that business logic. b) sounds like the need for a trigger. –  jcolebrand May 30 '11 at 6:11
    
@jcolebrand The purpose is to do a offline pull onto mobile devices of a data set. The pull needs to grab all created and modified records since the last pull. –  Kevin Sylvestre May 30 '11 at 6:31
8  
It is not a very good idea to develop and test with a different DBMS than the one used in production. –  a_horse_with_no_name May 30 '11 at 8:47
add comment

1 Answer 1

You need to combine a sequence with some mechanism to trigger the increment for an update. For postgres

create table my_table(id serial primary key, description text, touched serial);

Then either:

  • create a trigger to set touched = default on an update or
  • get all your updates going through an API that also actions the increment

auto-increment is implemented differently for SQLite so you will have to work around that - perhaps by inserting/deleting a new row each time you update a row, and using the rowid of that temporary row to update touched.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.