What are the general tactics with database handling? Very broad question!! Let me give you some details. I have a database with about 6 tables. and of them records in 5 tables are related to record in 1 main table (employee). The main table (employee) whenever updated, updats data in the other 5 tables like balances, payments, loans and such details. I have a log table that keeps track of each changes occuring in the records. When a user requests delete, I don't delete record just set the record status to be 'd' for delete for example. But record is still there in the db. Whenever, I have edits, new, deletes for records, I deal with each records in each of these different tables individually. Now the problem! How do I make sure that the data stays consistent in the database, which means I do not want to bomb out if after updating 3 tables, I got error while updating 4th table. This would leave inconsistent data in the database! How do I ensure that this does not happen? If it does happend, what's the best way to deal with it. I am mostly self-learned with db and not a pro, so not sure if this is the best way to do. Let me know your comments or point me in right direction Help me out please!!!
The concept you want to research is called a transaction. Pay particular attention to the parts about commit and rollback. Basically, begin a transaction INSERT, UPDATE, etc. If no errors: COMMIT; If errors: ROLLBACK;