I have started working on a postgreSQL database. There is a table keeping the record of stocks and every time a record is updated, a copy of the original row needs to be kept, with a flag added to say that the record is no longer current. I am planning to implement this using a Trigger (which I am assuming is the best option).
In the above case, with a flag to say that a record is not the current one, what's the best way to ensure that reads from the table always used the latest records? Only checking the flag value with where condition?