Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

3 Create a trigger named trg_mem_balance that will maintain the correct value in the membership balance in the MEMBERSHIP table when videos are returned late.

image text in transcribed

3 Create a trigger named trg_mem_balance that will maintain the correct value in the membership balance in the MEMBERSHIP table when videos are returned late. The trigger should execute as an AFTER trigger when the due date or return date attributes are updated in the DETAILRENTAL table. The trigger should satisfy the following conditions: a Calculate the value of the late fee prior to the update that triggered this execution of the trigger. The value of the late fee is the days late multiplied by the daily late fee. If the previous value of the late fee was null, then treat it as zero (O). b. Calculate the value of the late fee after the update that triggered this execution of the trigger. If the value of the late fee is now null, then treat it as zero (O). c. Subtract the prior value of the late fee from the current value of the late fee to determine the change in late fee for this video rental d. If the amount calculated in Part is not zero (O), then update the membership balance by the amount calculated for the membership associated with this rental. **Please note, this trigger must include an update cursor and must be written for SQL Server Examples of related tables: DetailRental Rent_Num Vid_Num Detall_Fee Detall_DueDate Detall_ReturnDate Detall_DallyLateFee 34342 2013-03-04 2013-03-02 1001 200 1.00 Membership Mem_Num Mem_FName Mem_LName Mem_Street Mem_City Mem_State Mem_ZIp Mem_Balance 103 6.00 Rental Rent_Num Rent_Date Mem_Num 1001 2013-03-01 103

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access with AI-Powered Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Accounting questions