Database update

Scenario:

I have Database1 (PostgreSQL). For this i) When a record is deleted, the status col. for that record is changed to inactive. ii) When a record is updated, the current record is rendered INACTIVE and a new record is inserted. iii) Insertion happens as usual. There is a timestamp col for each record for all the tables in the database.

I have another database2 (SQLite) which is synced with Database1, and follows the same property of Database1

Database1 gets changed regularly and I would get the CSV files for all the tables. The CSV would include all the data, including new insertions, and updations.

Requirement:

I need to make the data in Database1 consistent with the new CSV. i) For the records that are not in the CSV, but are there in Database1 (DELETED开发者_JS百科 RECORDS) - These records I have to set the status as inactive. ii) For the records that are there in the CSV but not there in the Database1 (INSERTED RECORDS) - I need these records to be inserted. iii) For the records that are updated in the CSVs I need to set status as inactive and insert new records.

Kindly help me with the logical implementation of these!!!

Thanks

Jayakrishnan

I assume you're looking to build software to achieve what you want, not looking for an off-the-shelf solution.

What environments are you able to develop in? C? PHP? Java? C#?

Lots of options in many environments that can all read/write from CSV/SQLite/PostgreSQL.

you could use an ON DELETE trigger to override existing delete behavior.

This strikes me as dangerous however. Someone is going to rely on this and then when the trigger isn't there, you will have actual deletions occur... It's better to encapsulate this behind a view or something and put a trigger on that. Or go through a stored procedure or something.