To maintain data integrity while tracking stock level changes in an inventory system, which combination should a DBA utilize?

• A. BEFORE UPDATE trigger and sp_executesql stored procedure
• B. AFTER UPDATE trigger and sp_executesql stored procedure
• C. AFTER DELETE trigger and stored procedure
• D. BEFORE INSERT trigger and sp_executesql procedure

Answer: (B)

Using an AFTER UPDATE trigger in combination with a stored procedure such as sp_executesql is an effective choice for maintaining data integrity during stock level changes in an inventory system.

An AFTER UPDATE trigger is executed immediately after a record is updated, allowing for verification and validation of the new data. In the context of an inventory system, this means that once the stock level of an item is changed, the trigger can perform essential checks to ensure that the new stock level does not violate any business rules, such as inventory thresholds or limits. For example, it could check if the stock level falls below a minimum threshold and take corrective actions or log events accordingly.

The use of sp_executesql within this context allows the database administrator to execute dynamic SQL within the trigger. This capability can be particularly useful for conditional logic that may depend on the updated data, enabling complex queries or modifications based on the updated stock level. For example, it can update related tables, log the changes, or trigger notifications based on specific criteria resulting from the original update.

The combination of an AFTER UPDATE trigger and the sp_executesql stored procedure thus provides a robust mechanism for ensuring that the inventory data remains accurate and reliable after modifications have been made.