2. Create a trigger 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 times 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 the rental that this detail is a part of. e) Test your trigger.
plz help me fix the following trigger problem is in picture
CREATE OR REPLACE TRIGGER MEM_BALANCE
AFTER UPDATE ON LATE_FEE
FOR EACH ROW
BEGIN
UPDATE DETAILRENTAL
SET LATE_FEE = CASE
WHEN LATE_FEE = (DETAIL_DUEDATE – DETAIL_RETURNDATE) * DAILY_LATE_FEE WHEN LATE_FEE = (DETAIL_DUEDATE – DETAIL_RETURNDATE) * DAILY_LATE_FEE IS NULL THEN LATE_FEE := 0 WHEN UPDATED_LATE_FEE:= ORIGINAL_LATE_FEE – LATE_FEE
WHEN UPDATED_LATE_FEE:= ORIGINAL_LATE_FEE – LATE_FEE IS NOT NULL THEN MEM_BALANCE:= TOTAL_BALNCE – UPDATED_LATE_FEE ELSE NULL END;
the
CREATE TABLE MEMBERSHIP
( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY,
MEM_FNAME VARCHAR(30) NOT NULL,
MEM_LNAME VARCHAR(30) NOT NULL,
MEM_STREET VARCHAR(15),
MEM_CITY VARCHAR(10),
MEM_STATE CHAR(2),
MEM_ZIP CHAR(5),
MEM_BALANCE NUMBER (2));
ALTER TABLE MEMBERSHIP
MODIFY MEM_STREET VARCHAR(25);
CREATE TABLE RENTAL
( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY,
RENT_DATE DATE,
MEM_NUM CHAR(3),
CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP);
CREATE TABLE PRICE
(PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY,
PRICE_DESC VARCHAR(20),
PRICE_RENTFEE NUMBER (3,1),
PRICE_DAILYATFEE NUMBER(3,1));
CREATE TABLE MOVIE
(MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY,
MOVIE_NAME VARCHAR(30) NOT NULL,
MOVIE_YEAR CHAR(4),
MOVIE_COST NUMBER(5,2),
MOVIE_GENRE VARCHAR(15),
PRICE_CODE CHAR(1),
CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES PRICE);
CREATE TABLE VIDEO
(VID_NUM CHAR(5) CONSTRAINT VIDEO_VIDEO_NUM_PK PRIMARY KEY,
VID_INDATE DATE,
MOVIE_NUM CHAR(4),
CONSTRAINT VIDEO_MOVIE_NUM_FK FOREIGN KEY (MOVIE_NUM) REFERENCES MOVIE);
CREATE TABLE DETAILRENTAL
(RENT_NUM CHAR(4),
VID_NUM CHAR(5),
DETAIL_FEE NUMBER(3,1),
DETAIL_DUEDATE DATE,
DETAIL_RETURNDATE DATE,
DETAIL_DALYLATEFEE NUMBER(2),
CONSTRAINT DETAILRENTAL_RENT_VID_PK PRIMARY KEY(RENT_NUM, VID_NUM),
CONSTRAINT DETAILRENTAL_RENT_FK FOREIGN KEY (RENT_NUM) REFERENCES RENTAL,
CONSTRAINT DETAILRENTAL_VID_FK FOREIGN KEY (VID_NUM) REFERENCES VIDEO);
INSERT INTO MEMBERSHIP VALUES('102', 'Tami', 'Dawson', '2632 Takli Circle', 'Norene', 'TN', '37136', 11);
INSERT INTO MEMBERSHIP VALUES('103', 'Curt', 'Knight', '4025 Cornell Court', 'Flatgap', 'KY', '41219', 6);
INSERT INTO MEMBERSHIP VALUES('104', 'Jamal', 'Melendez', '788 East 145th Avenue', 'Quebeck', 'TN', '38579', 0);
INSERT INTO MEMBERSHIP VALUES('105', 'Iva', 'Mcclain', '6045 Musket Ball Circle', 'Summit', 'KY', '42783', 15);
INSERT INTO MEMBERSHIP VALUES('106', 'Miranda', 'Parks', '4469 Maxwell Place', 'Germantown', 'TN', '38402', 5);
INSERT INTO RENTAL VALUES('1001', TO_DATE('01-MAR-09', 'DD-MM-YY'), '103');
INSERT INTO RENTAL VALUES('1002', TO_DATE('01-MAR-09', 'DD-MM-YY'), '105');
INSERT INTO RENTAL VALUES('1003', TO_DATE('02-MAR-09', 'DD-MM-YY'), '102');
INSERT INTO PRICE VALUES('1', 'Standard', 2.0, 1);
INSERT INTO PRICE VALUES('2', 'New Release', 3.5, 3);
INSERT INTO PRICE VALUES('3', 'Discount', 1.5, 1);
INSERT INTO PRICE VALUES('4', 'Weekly Special', 1, 0.5);
INSERT INTO MOVIE VALUES('1234', 'The Cesar Family Christmas', 2007, 39.95, 'FAMILY', '2');
INSERT INTO MOVIE VALUES('1235', 'Smokey Mountain Wildlife', 2004, 59.95, 'ACTION', '3');
INSERT INTO MOVIE VALUES('1236', 'Richard Goodhope', 2008, 59.95, 'DRAMA', '2');
INSERT INTO MOVIE VALUES('1237', 'Beatnik Fever', 2007, 29.95, 'COMEDY', '2');
INSERT INTO MOVIE VALUES('1238', 'Constant Companion', 2008, 89.95, 'DRAMA', '2');
INSERT INTO MOVIE VALUES('1239', 'Where Hope Dies', 1998, 25.49, 'DRAMA', '1');
INSERT INTO MOVIE VALUES('1245', 'Time to Burn', 2005, 45.49, 'ACTION', '3');
INSERT INTO MOVIE VALUES('1246', 'What He Doesnt Know', 2006, 58.29, 'COMEDY', '1');
INSERT INTO VIDEO VALUES('54321', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');
INSERT INTO VIDEO VALUES('54324', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');
INSERT INTO VIDEO VALUES('54325', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');
INSERT INTO VIDEO VALUES('34341', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');
INSERT INTO VIDEO VALUES('34342', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');
INSERT INTO VIDEO VALUES('34366', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('34367', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('34368', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('34369', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('44392', TO_DATE('21-OCT-08', 'DD-MM-YY'), '1237');
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
I need help with this question in mysql code:
Thank You
by Bartleby Expert
