We have 2 tables linked by referential integrity. When the referenced record is attempted to be deleted, a PRE-Delete table trigger is executed to also delete the related record. Thus removing the reference issue.
However, the trigger fails citing referential integrity. Is there a timing issue going on? Is the container not aware that the reference which is causing the problem is also being deleted?
What are some other options to accomplish this? The requirement is to allow delete from the list form control action for delete.
Yes, in short this is a timing issue resulting from the trigger being pre-delete instead of post-delete.
It sounds like this might be set up backwards, but that depends on the architecture. It makes sense to have a pre-delete trigger on the referencing table to remove the dependent record before deleting, but the referenced record does not need a trigger to delete the other record; it could delete by itself with no concern to referential integrity.
If your situation is cause for deleting the referencing record regardless (Table A references Table B. When deleting a record from Table B, you want to remove the record from Table A regardless), you should use a post-delete trigger instead. This should allow the referenced record to be deleted before the referencing record.
The goal is to delete Table A record AND Table B record as part of the same list form delete action from Table A. Table B is a cross reference type table so its existence is entirely dependent on Table A record.
@ross does that clarify anything for you? It seems like the above falls into the category you thought might be set up backwards
It does fall into that last paragraph - B is dependent on A existing and B references A directly. Deleting A in a vacuum will lead to a referential integrity error. Deleting B before deleting A should mitigate that error.
It’s interesting the pre-delete trigger idea didn’t work, but that might be because the server still recognizes record A being deleted and does the check while record B still references it.
One solution would be to call a new logic block from the pre-delete trigger with Call in new transaction boundary true and Adopt All Messages also true. This will ensure that the table B record is completely gone before the system tries to remove A and validate its references. Note: using this pattern opens the door for data integrity issues if the pre-delete trigger fails for any reason after deleting the table B record. The delete should be performed after all validations and directly before the end of the trigger.