WebSphere Commerce: dbclean causing order items to disappear from production in a staging environment

In a staging environment, WebSphere Commerce has a staging database and a production database. However, the order items are only in the production database because the customers only access the production server(s).

When a catalog entry that is marked for delete is deleted from the staging database, a database trigger inserts an entry in the STAGLOG table which propagates the deletion to the production database the next time stageprop runs. However, since the CATENTRY table is a parent of the ORDERITEMS table, this automatically deletes the order items in the production database linked to the deleted catalog entry. This causes order items to disappear from orders in the production database.

One way to prevent this is to remove the database delete trigger in the staging database on deletion of CATENTRY (and other tables like OFFER) which are parents of the ORDERITEMS table. This causes the running of dbclean on the staging database to delete only the CATENTRY (and other tables like OFFER) and not propagate those deletions to the production database. This means that a separate dbclean would run on the production database to delete those CATENTRY entries marked for delete only if there is no corresponding ORDERITEMS entry.

The flaw in removing the database delete trigger on those tables is that if a CATENTRY (and other tables like OFFER) has dbclean run on it before stageprop has a chance to propagate the change from active to marked for delete status, those entries would remain active in the production database when the corresponding entries in the staging database have been deleted.

Another way to prevent the mysterious disappearance of order items is to retain the database triggers and propagate the user information, orders and order items from the production database back to the staging database before dbclean is run. This ensures that the checks for existing order items linked to CATENTRY (and other tables that are parent to the ORDERITEMS table) done during dbclean occurs on the staging side.

This disappearance of order items caused by dbclean is an obscure problem that is probably not felt by most customers because they tend to have a backend ordering system like Sterling Order Management. Once the order is created in the backend system, there usually is no need to refer back to the order in WebSphere Commerce. The missing order items would stay in WebSphere Commerce and not be propagated to a backend ordering system.