Unanswered: Record versioning using multiple dbs with triggers
I am currently in the planning stages for a web based application which reallt needs record versioning.
However I do not want the versioning system to over complicate the whole model and all of the queries. So I had an idea that someone who knows better might tell me is completely crazy, but here goes:-
Run 2 identical databases: 'current version' 'old version' except that the 'current' database would have triggers on all the tables so that when a record was changed it put the current version of the record (which is about to be updated) into the 'old version' database. So there would be just 2 record versions; the current and previous.
Then to make this more sophisticated I was thinking that I could run CDP hot backups on the 'old version' database, so then if for whatever reason they needed a version from last week, we could go back to that restore point on the 'old version' db and then they would be able to view those versions in the application.