Integrity constraint error in a FK to AuditMaster from custom table

[richa.khurana]

Hi,

I have the following scenarios-

Scenario1:

1. There is a table A which has a field revNumber defined as a foreign key(FK_1) to AUDIT_MASTER table

2. There is a hibernate interceptor(extending EmptyInterceptor) which is invoked on saving conversations. This interceptor creates records in Table A.

Usually, there are no issues. However, we run into intermittent issues as-

ORA-02291: integrity constraint (<Schema>.FK_1) violated  - parent key not found

I tried to turn sql on and we always have insert statement for LP_AUDIT_MASTER before insert into A(which the interceptor inserts).

Kindly suggest.

Scenario2:

There is a class A(marked auditable). Corresponding Tables are T_A and T_A_Aud. Randomly at certain times we see that some records dont get inserted into T_A_AUD(No revision at all) though the corresponding record does exist in T_A. Can you kindly advise how we can troubleshoot the issue to understand why this happens. The scenario is not restricted to just one entity A here. It sporadically appears for any of the entities.

Thanks,

Richa.

[adamw]

Ad1:

What is LP_AUDIT_MASTER? Normally inserting into revisions table before audit tables is the good order. What DB are you on?

Ad2:

That's really weird. Do you have proper TX management?

Adam