More complex logic

In the previous example, the only complexity in the stored procedure was the use of two tables, order header and lines. In practice, the logic can be more complex.

Data may already exist in the database. Consequently, instead of inserting, it may be required to update the existing data. This may especially be complex in case of transactional data, where a document usually includes a header and multiple lines. In such cases a change does not only require updates of existing header and lines, but also the deletion or the addition of lines.

The incoming BOD has an actionCode that you can use. But note that the actionCode is set from the perspective of the BOD sender. If the sender indicates that a document is changed it might be a new document for the receiver, because of content-based routing or filtering that might be performed in between.

Consequently, in the stored procedure you can for example implement this logic:

• If the actionCode is Delete, delete the object if it exists.
• For other actionCodes, check whether the object already exists. If so, update the existing object, otherwise insert the object.
• If an existing object must be updated, compare the lines from the incoming document to the lines existing in the database and depending on that, perform inserts, updates, or deletions of lines.

Finally, ensure the stored procedure implementation does not violate the consistency of the data in the database. Updating a database directly bypasses the logic of the application that owns the database. If in the document flow the writing to the database is preceded by a mapping activity, you should check the defined mapping to ensure that the resulting data is valid for the database.