Publish record deletions using SQL

Semarchy xDM supports publishing master- and golden-record deletions using SQL.

Publish golden-record deletions

Golden-record deletion applies to all entities and is similar to deleting a golden record from an application.

To publish a golden-record deletion, you must load the following columns into the SA table (even for ID- and fuzzy-matched entities):

B_LOADID: load this column with the load ID provided by the GET_NEW_LOADID function call, or with the ID of the continuous load. Use the GET_CONTINUOUS_LOADID function to retrieve a continuous load ID from the continuous load name.
B_DELETETYPE: load this column with the SOFT_DELETE or HARD_DELETE value depending on the type of delete that you want to trigger.
B_DELETEOPERATION: delete operation ID. This ID must be a unique value for each deleted record. For example, use a UUID that you convert to a string (e.g., Using the functions RAWTOHEX(SYS_GUID())).
B_CLASSNAME: load this column with the name of the entity of the records to delete.
<GoldenID>: load this column with the ID of the golden record to delete.

You can optionally load the B_DELETEAUTHOR and B_DELETEDATE columns to provide an author and a date for the delete operation.

With golden-record deletion, the delete type (and delete operation ID) is automatically propagated to the child records according to the delete propagation configured on the references.

If a delete fails (e.g., because a child record prevents the delete), the deletion error is traced in the AE (i.e., authoring error) table for the entity.

For more information about record deletion, see Record deletion.

Publish master-record deletions

For matching entities, you can publish master-record deletions.
To do so, use the process to delete golden records, with the following differences:

Load the SD table instead of the SA table.
Instead of the <GoldenID> column, load the B_PUBID column, plus:
- The B_SOURCEID column for a fuzzy-matched entity.
- The column representing the attribute defined as the primary key attribute for an ID-matched entity.

Load the B_LOADID, B_DELETETYPE, B_DELETEOPERATION, B_CLASSNAME, B_DELETEAUTHOR, and B_DELETEDATE columns as described to delete golden records.

When deleting one of the master records contributing to a golden record, the values consolidated in the golden record are updated to take into account this master-record deletion.

Master-record deletion does not support the delete propagation defined in the reference relationships. Master-record deletes are not blocked by a restriction, propagated by a cascade, and references to deleted records are not automatically nullified. Each master-record deletion is considered individually. Users should perform in the same load all the changes that need to take place with the master deletion.

For more information about record deletion, see Record deletion.