PLEASE DO THIS IN SQL MANAGEMENT STUDIO: Create a trigger named trg_mem_balance

Question

PLEASE DO THIS IN SQL MANAGEMENT STUDIO:

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

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

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 c is not zero (0), then update the membership balance by the amount calculated for the membership associated with this rental.

Explanation / Answer

CREATE OR REPLACE TRIGGER TRG_MEM_BALANCE
AFTER UPDATE OF DETAIL_DUEDATE, DETAIL_RETURNDATE
ON DETAILRENTAL

FOR EACH ROW

DECLARE
PRIOR_LATEFEE NUMBER;
NEW_LATEFEE NUMBER;
UPDATE_AMOUNT NUMBER;
RENTAL_MEMBER RENTAL.MEM_NUM%TYPE;

BEGIN
PRIOR_LATEFEE := :OLD.DETAIL_DAYSLATE * :OLD.DETAIL_DAILYLATEFEE;
IF PRIOR_LATEFEE IS NULL
THEN PRIOR_LATEFEE := 0;
END IF;

NEW_LATEFEE := :NEW.DETAIL_DAYSLATE * :NEW.DETAIL_DAILYLATEFEE;
IF NEW_LATEFEE IS NULL
THEN NEW_LATEFEE := 0;
END IF;

UPDATE_AMOUNT := NEW_LATEFEE - PRIOR_LATEFEE;

IF UPDATE_AMOUNT <> 0
THEN
SELECT MEM_NUM INTO RENTAL_MEMBER
FROM RENTAL
WHERE RENT_NUM = :NEW.RENT_NUM;

UPDATE MEMBERSHIP
SET MEM_BALANCE = MEM_BALANCE + UPDATE_AMOUNT
WHERE MEM_NUM = RENTAL_MEMBER;

END IF;
END

Explanation:

As required, if rows related to due date/return date are updated, this trigger gets called.
Hence, the trigger is called AFTER UPDATE OF DETAIL_DUEDATE, DETAIL_RETURNDATE ON DETAILRENTAL table

So,first initial late fee value is calculated. If this is NULL,it is set to 0
Then, late fee with the latest values is calculated. And is set to 0 if it's NULL.

Then,difference between former and late fee is calculated and stored in update_amount and is updated in Membership table

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.