Handle delete of statistical code associated with instance, holdings, or item properly..

Overview:

There is nothing in place to remove a statistical code from instance, holdings, or item when they are deleted from the settings.

Additional Information:

There are several options.

A) "Ignore/don't care": Keep the (orphaned) statistical codes in the instance, holdings and item records, and delete it from the statistical code settings. This is useless because the record can no longer been changed (to update status (loan!), location, etc.) because any update will be rejected due to the non-existing statistical code (unless we change the insert and update APIs to automatically remove any non-existing statistical codes).

B) "Block delete": Add a database trigger that automatically runs when a statistical code is deleted from the settings. It blocks the delete if the statistical code is still in use in any instance, holdings or item record.

C) "Delete cascade": Add a database trigger that automatically runs when a statistical code is deleted from the settings. It deletes the statistical code from all instance, holdings or item records.

Implementation option:

B) and C) might be implemented by an item_statistical_code table that consists of two foreign key columns: itemId and statisticalCodeId. A trigger that runs on any item insert, update and delete can copy the content of the statisticalCodeIds array into the table.

Interested parties:

julianladisch lauraw