$12.$ Create a trigger named trg$\_$late$\_$returnthat will write the correct value to DETAIL$\_$DAYSLATE in the DETAILRENTAL table whenever a video is returned. The trigger should execute as a BEFORE trigger when the DETAIL$\_$RETURNDATE or DETAIL$\_$DUEDATE attributes are updated. The trigger should satisfy the following conditions:

If the return date is null, then the days late should also be null.

If the return date is not null, then the days late should determine if the video is returned late.

If the return date is noon of the day after the due date or earlier, then the video is not considered late, and the days late should have a value of zero $(0).$

If the return date is past noon of the day after the due date, then the video is considered late, so the number of days late must be calculated and stored.

$13.$ Create a trigger named trg$\_$mem$\_$balancethat 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:

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 $(0).$

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 $(0).$

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.

If the change in late fee, calculated above, is not zero $(0),$ then update the membership balance by the amount calculated for the membership associated with this rental.

$14.$ Enable auto increment for rental numbers in the RENTAL table. Start the values with $1100.$

$15.$ Create a stored procedure named prc$\_$new$\_$rentalto insert new rows in the RENTAL table. The procedure should satisfy the following conditions:

The membership number will be provided as a parameter.

Use a Count$()$ function to verify that the membership number exists in the MEMBERSHIP table. If it does not exist, then a message should be displayed that the membership does not exist and no data should be written to the database.

If the membership does exist, then retrieve the membership balance and display a message that the balance amount is the previous balance. $($E$.$g$.,$ if the membership has a balance of $$5\mathrm{.}00,$ then display $$Previous balance: $$5\mathrm{.}00.)$

Insert a new row in the rental table using the current system date for the RENT$\_$DATE value, and the membership number provided as the value for MEM$\_$NUM. The rental number should be provided automatically from the auto increment enabled in the previous problem.

$16.$ Create a stored procedure named prc$\_$new$\_$detailto insert new rows in the DETAILRENTAL table. The procedure should satisfy the following requirements:

The video number will be provided as a parameter.

Verify that the video number exists in the VIDEO table. If it does not exist, then display a message that the video does not exist, and do not write any data to the database.

If the video number does exist, then verify that the VID$\_$STATUS for the video is $$IN$.$ If the status is not $$IN$,$ then display a message that the video$$s return must be entered before it can be rented again, and do not write any data to the database.

If the status is $$IN$,$ then retrieve the values of the video$$s PRICE$\_$RENTFEE, PRICE$\_$DAILYLATEFEE, and PRICE$\_$RENTDAYS from the PRICE table.

Calculate the due date for the video rental by adding the number of days in PRICE$\_$RENTDAYS to $11$:$59$:$59$PM $($hours:minutes:seconds$)$ in the current system date.

Insert a new row in the DETAILRENTAL table using the previous value returned by RENT$\_$NUM$\_$SEQ as the RENT$\_$NUM, the video number provided in the parameter as the VID$\_$NUM, the PRICE$\_$RENTFEE as the value for DETAIL$\_$FEE, the due date calculated above for the DETAIL$\_$DUEDATE, PRICE$\_$DAILYLATEFEE as the value for DETAIL$\_$DAILYLATEFEE, and null for the DETAIL$\_$RETURNDATE.

$17.$ Create a stored procedure named prc$\_$return$\_$videoto enter data about the return of videos that have been rented. The procedure should satisfy the following requirements.

The video number will be provided as a parameter.

Verify that the video number exists in the VIDEO table. If it does not exist, display a message that the video number provided was not found and do not write any data to the database.

If the video number does exist, then use a Count$()$ function to ensure that the video has only one record in DETAILRENTAL for which it does not have a return date. If more than one row in DETAILRENTAL indicates that the video is rented but not returned, display an error message that the video has multiple outstanding rentals and do not write any data to the database.

If the