Enforcing Data Integrity (longish)

Hi all! I've got a tenacious little problem that I've been fighting for over a year, and hoping to get a new spark of light from the wisdom of Le Net. I've got an ugly little schema that has three tables that look like this: INV <1:-----:Many> DIST <Many:----:Many> DETAIL The relationship from the DIST table to the DETAIL table is rather loose. There isn't any data in DETAIL that points directly to any specific DIST, and vice a versa. That linkage is based on yet another table not shown, as DIST is used to distrbiute the DETAIL data across the INV data. Anyway, something that is true is that the quantities in the INV table should balance with the quantities in the DIST table which in turn will balance with the DETAIL table. And this works just great, 99.9% of the time. Of course, it's the 0.1% that keeps the phone ringing. I've put nasty little audits into the two places where these tables are changed in the code, that plastered huge ugly boxes across the screen screaming out "OOH! OOH! I FOUND IT! CALL x1234" to try and hunt down the little bugger that is corrupting my data. These audits were called after my last SQL statement, but just before the COMMIT WORK. The hope was that whenever I would be making my plethora of changes, and then check everything I'd changed to see if it balanced. If the audit failed, everything was rolled back, the user rudely booted out, lots of data was dumped to audit logs, and I got a nasty phone call. The audits worked GREAT! 100% they caught the system when it was out of balance. The audits always found the system when it was out of balance, but never when it was GOING out of balance. It was always after the fact. When the audit blew up, I'd run a seperate, external audit and sure enough, the system was out of balance, even after everything was rolled back. Which means it waws out of balance before the original program got its mitts on the data. Extremely frustrating. What I'd like to do is set up some contraints and triggers to help enforce the balance. Maybe some rogue process or user is putting its pebble on only one side of the scale, but if the system were self-enforcing, then we'd never have this problem...maybe. But, I can't use a trigger because a trigger would fire when only one row was updated, not waiting for the rest to catch up. And it seems that I can't use a constraint, because I can't use subqueries against other tables in the CHECK statment. Contraints would be perfect because they can be delayed until the COMMIT WORK. Yeah,the performance may suck for a while, but I think I'd have a better chance of nabbing the culprit. At this point, all I can think of is some contention problem with dirty data. INFORMIX has some pretty good locking, and all of the shenanigans happen in one big flush at the end of a transaction, fast enough to that the LOCK WAITs don't pose a problem. But, maybe in its zeal, while one transaction is flushing out, another may be reading a new row that holds only half the data. But, in theory my audit would have caught those as they were happening...and it doesn't. So, I think I'm doomed to coercing my forehead to be flatter and flatter with the help of an unyielding wall. But, I figured a quiet plea out to the net for some idea that I may not have thought of would be noteworthy (and much appreciated by my co-workers as my pleas across the bull pen are less quiet...). Anyway, if you've made it this far...thanx, and I'd appreciate any pointers anyone may be able to provide. Will (villy@collinscomp.com)