Cascading update and delete
Cascading Update and Delete: Overview (Oracle and Microsoft SQL Database Servers)
Archibus includes a cascading update and delete feature for installations running Oracle and Microsoft SQL database servers.
The Archibus cascading update and delete feature preserves referential integrity when you make manual changes to primary keys that validate other fields, contribute to multipart keys, or are used to link assigned or standard tables. Update cascade updates all child records to reflect the change made to the parent record. Delete cascade works differently depending on whether the record you are deleting contributes its primary key to a multipart primary key for another record, or if it links to another records as a foreign key.
Since Archibus cascading update and delete are long-running operations, they are invoked as jobs to avoid performance issues. Users can view the actual progress for a job by viewing its job status, and can continue working while the job executes.
For information on setting up Archibus cascading update and delete, see Setting Up Archibus Cascading Update and Delete.
How Cascading Delete Works
Cascading delete works differently when you delete records that contribute to other records multipart primary keys, and when you delete records that are foreign keys that link to another record without contributing to that record's primary key.
Primary Keys
Deleting a record whose primary key identifies assigned records means that the assigned records no longer exist. For these records, Archibus cascades through the database and deletes all records that require this deleted value as part of their primary key. For example, the Buildings table contributes its primary key to the multipart primary keys for its floor and room records. If you delete the record for building HQ, then Archibus also deletes the floor and room records that include building HQ's primary key as part of their primary key.
Foreign Keys
Archibus follows a slightly different behavior for changing values that link two tables but do not contribute to primary keys. Changing a primary key updates the foreign keys in all of the linked tables; however, deleting a primary key does not necessarily delete records using this value as a foreign key.
For example, suppose value HQ-17-104 identifies a record in the Rooms table, and three records in the Equipment table have this value for their Room Code field—three equipment items are stored in this room.
If you edit the Room data by changing 104 to 999, for all tables in the project database, the program immediately updates the value of the Room Code field from 104 to 999. In the Equipment table, the four equipment items are now assigned to room 999.
However, suppose you deleted room 104; since equipment can exist without a room assignment, Archibus does not delete the equipment records whose Room Code value is 104; instead, since room 104 no longer exists, the program changes the Room Code value in the Equipment table to Null.
In its definition in the Archibus Fields schema table, each field specifies whether or not it can accept null values . If a field serving as a foreign key field can accept null values, the program changes the foreign key to Null when the associated primary key is deleted.
If a foreign key field cannot accept a null value, Archibus deletes the record when the record holding the primary key is deleted. For example, groups (departmental areas on floors) are not identified with a multipart key requiring a floor code, but have no meaning if their floors are removed. Accordingly, the definition of the Floor Code field in the Groups table is set to not accept null values. Therefore, if you delete a floor record, you delete group records whose foreign keys store this value.
The following table summarizes how editing or deleting a primary key affects records using this value as a primary key or as a foreign key.
| Primary Key Edit | Effect on Primary Keys | Effect on Foreign Keys |
|---|---|---|
|
Delete |
Deletes all records whose primary keys depend on this value. |
If the foreign key field cannot accept Null, deletes all records whose foreign keys have this value. If the foreign key field can accept Null, completes the foreign key field of all affected records with Null. |
|
Edit Value |
Updates all records whose primary keys depend on this value. |
Edits the foreign keys of all affected records with the new value. |
In
/confing/context/core/core-optional.xml
, the bean
cascadeDeleteDoNotAllowNullFieldsNames
disables the
allow_null
property of the fields for cascading delete for the listed fields. That means the child record will be deleted if the parent record is deleted no matter the allow null value.
When Cascading Update and Delete Is Called
Cascading update and delete is fired from the following view commands:
<command type=”saveForm”/>
<command type=”deleteRecord”/>
<command type=”deleteSelectedRows”/>
or from Java when the following DataRecord changes are made:
DataSource.deleteRecord(record)
DataSource.saveRecord(record)
DataSource.updateRecord(record)
Any custom SQL statements such as DELETE FROM or UPDATE SET will not fire the cascade handler.
See Also
Setting Up Archibus Cascading Delete and Update
For information on merging primary keys if a cascading update fails, see the following location on the Contents pane: Web Central User's Guide /Using Web Central / Business Process Owner / Merging Primary Keys .