Cascade Delete for Document ID field

[andyliu]

Since Eureka, we can configure Cascade Delete for any reference field. However, I don't see that option available in the configuration of a document ID field.

I feel that a document ID field is basically a reference field plus a depended field that indicates which table the reference field points to. Is there any reason Cascade Delete cannot be supported for document ID field? Or is there any trick we can do to enable it for document ID field?

Thanks,

Andy

[Brian Dailey1]

Hi Andy,

I don't disagree, but it might be a little cumbersome.   You could try the following in a global BR set to run on a record Delete.

1. From the Dictionary, grab a list of all the fields that are of type Document ID.
2. Query the table that each field from (1) belongs to for   "<fieldname>=<deleted record's sys_id>"
3. These query results are your lists of referring records to be considered/addressed in your cascade delete.

Thanks,

-Brian

[andyliu]

Hi Brian,

Thanks for the reply. What you suggested here should work but could have some performance impacts. I think I can probably do something similar with some twists:

In my case, I have about 4-5 tables that have document ID fields and can point to each others' records. So instead of using a global BR, I will create one BR per table, and they will only scan the records within these 4-5 tables.

Update:

Actually even if putting BR on 4-5 tables and scan their records seem too costly for me. I think I will have to find out some other way which does not requires to always scan all the 4-5 tables. (like use some other logic to find out which specific table will contain the records and scan that one table only)

I guess that is probably why Cascade Delete is not supported for Document ID field: since each record could refer to a record in different tables, there is no way we can know whether a table contains records to delete without scan through all the records in it.

Thanks,
Andy

[nyancer3]

I know this is an old thread, but I have come across a similar issue. In my case, I cannot decide whether or not I want to block deletion if there are records referred to the current record by document ID or if I want to delete the referring records.

Regarding performance, if you do want to parse through the dictionary to look for other tables with document_id and cascade delete any records that refer to the one you are deleting then you can push this effort outside the transaction using events. use an onAfter business rule to log an event with the table name and sys_id as parameters, then you can process the event in a script action that scrapes the dictionary for any tables that refer to document ID and query/delete from those tables. All of this will happen outside the user transaction (in the background) and will not block the client session in any way. As long as you are not deleting a ton of records, this should not impact system performance.

Just a thought.