In a transaction the database updates (ejbStore() calls) occur at the end of
 the transaction, and occur in the order the updates were called. Clearly th
e order in which the updates occur must be maintained due to possible refere
ntial integrity constraints
within the DB.

My current problem is that calls to remove() result in an immediate call to 
ejbRemove(), which causes an out-of-order operation with some updates that o
ccurred within the same TX.

My tables are structured like the following (just an example):

EMPLOYEE:
__________________________
| EMPLOYEE_PK | MANAGER_FK |
+-------------+------------+
| E1          | M3         |
| E2          | M3         |O---+
| E3          | M4         |    |
| E4          | M4         |    |
--------------------------     |
|
MANAGER:                        |
____________                   |
| MANAGER_PK |                  |
+------------+                  |
| M3         |------------------+
| M4         |
------------



Now if I want to delete M3 I need to clear the MANAGER_FK field in the appro
priate EMPLOYEE records. For example:


1     String managerToDeleteId = "M3";
2
3     Collection c = EmployeeHome.findByManager(managerToDeleteId);
4     Iterator i = c.iterator();
5     while(i.hasNext())
6     {
7         Employee e = (Employee)c.next();
8         e.setManagerID(null);
9     }
10
11    Manager m = ManagerHomd.findByPrimaryKey(managerToDeleteId);
12    m.remove();



To my mind this should work just fine, but when the code is run the removal 
of the manager (via ejbRemove()) is called immediately, thus since ejbStore(
) for the Employees has not yet been called (waiting for the end of the TX) 
a foreign key constraint is
violated and a SQLException is thrown.


We have an alternative table structure which could avoid the issue (by using
 an association table, and putting control of it under the Manager) but we f
eel that is inappropriate.

Any suggestions?

[ Post a follow-up to this message ]