Nested Triggers/Procedures

This is hard one to explain without writting a Novel. We are running Oracle 7.3.2.3 on HP-UX B10.20. SQLNET 2.3.2.1.0. I have two cascading triggers and many called procedures/functions. There are only about 3 or 4 custom defined error exceptions.

The first trigger takes a varchar2 field and parses the data into a formated table. The second trigger (on insert) is where it will call various procedures/functions depending on a 'transaction-type'. The volume of inserts can range from one or two every minute to thousands being sent all at once.

The problem is sometimes a particular error is raised (one of the user defined ones), that error condition will be raised for other transactions where it is not applicabale. For exapmple I will have 150 rejected transactions, I re-run them and I have 4 rejected, I re-run them and now 1 is rejected (and this rejection is legitimate) BTW the only difference in re-running then is the main trigger is re-compiled as a procedure with a lopping cursor on the rejected table ) it almost seems like a 'context error' where some variable is not being reset or something like that. I have not been able to determine any consistency in what type of transactions get rejected (ie: it is not all of them after the inital error).

I can provide more details if required, but this message is long enough. Any tips or insight would be appreciated.

tia.........dy Received on Sun Feb 02 1997 - 00:00:00 CST