What trigger and stored procedure combination should a DBA use for logging price changes in an online bookstore?

What trigger and stored procedure combination should a DBA use for logging price changes in an online bookstore?

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

Answer: (B)

To effectively log price changes in an online bookstore, the choice of an AFTER UPDATE trigger combined with a stored procedure is pivotal. The AFTER UPDATE trigger is specifically designed to execute following any updates to a record. This timing is crucial because it allows the system to access the new price that is set as well as the existing price before the update occurred. By using this trigger, the DBA can ensure that all changes to the price are captured accurately, including the specific details of what changed.

Incorporating a stored procedure, such as sp_executesql, within this trigger enhances the logging process by allowing the dynamic execution of SQL statements with variable parameters. This means that the stored procedure can be utilized to insert the old price and the new price into a logging table, ensuring that a historical record of price changes is maintained. Using sp_executesql effectively enables parameterization, which is beneficial for performance and security.

Other options do not align perfectly with the requirements for tracking price changes. For example, a BEFORE UPDATE trigger doesn’t capture the state of the data after it has been updated, making it less suitable for logging purposes. Similarly, utilizing an AFTER DELETE trigger is misaligned, as it pertains to logging deletions rather than updates to prices. Lastly,