Adding Triggers to the Critique Table

Every time you delete or update a critique in the Critique table, the average overall rating column (avg_rating) in the title table is recalculated. It is not necessary to recalculate the rating when you add a critique because critique records are always updated before their content is used by the library application. The default value of the isApproved column is False, and the overall rating in the Critique table is not included in the calculation of the average overall rating (in the title table) until the Critique table is updated and the isApproved column is set to True.

To support this functionality, the following two triggers were added:

• Update trigger on the Critique table. When a critique record is modified an update trigger on the table selects all approved critiques for the title and recalculates and updates the average overall rating (avg_rating) in the title table. The overall average rating is rounded to the nearest half.
• Delete trigger on the Critique table. When a critique record is deleted a delete trigger on the table selects all approved critiques for the title and recalculates and updates the average overall rating (avg_rating) in the title table. When you delete the only critique for a title the average overall rating in the title table is set to 0. The overall average rating is rounded to the nearest half.