Today at work I found the following situation:
We had two tables,
B, with B having a foreign key linked to the primary key of A.
On our application, we got two main situations that created the problem :
One where we had to add add some records in A, followed by the creation of some records in B referring to the ones in A. This was just fine, since we created our BLL to handle first the changes in A and them B. The "problem" is, sometimes we will have to delete the records of B and them delete the referred record in A, wich raises an error, since we are trying to delete A before deleting B, what could break the referential integrity of these tables. Of course everything is inside a transaction, and we could deal with that in our BLL code, simply changing the order of wich changes cames first (in A or in B) based on the actions that have to be made, but my question is more conceptual than a "please help me solve my problem" one, since the "problem" is already solved (but of course, I'm accepting suggestions if they came up! =D).
Chatting in the office, we agreed that, inside a transaction, everything is being donne and if some problem occurs, what was done is rolled back.
My idea: Why can't the transaction evaluate everything and them order the statements and execute that in a way that don't break the referential integrity, since after the transaction everything will be done and the order might not matter at all? After thinking a little about it, even I am not sure if this thing would be nice or create some king of living hell, since I can think of some situations where the order of the statements would matter, so I'm posting it here so we can discuss about it.
I hope that my text is not confusing. And just to mention, I'm using
Microsoft SQL Server. Dunno if this is possible in other databases.