MySQL: Create a trigger so that if a customer has a fine, they cannot checkout a
ID: 3920898 • Letter: M
Question
MySQL: Create a trigger so that if a customer has a fine, they cannot checkout another book.
DELIMITER $$
CREATE trigger finecheck before insert on checkout
for each row
begin
IF <INSERT TRIGGER CONDITION HERE>
THEN
SIGNAL SQLSTATE '02000'
SET MESSAGE_TEXT = 'Error: Customer has unpaid fines. Cannot checkout.';
END IF;
END;
$$
Customer table:
Fines table:
Checkout table:
FINES_idFINES idCUSTOMER 123456789012345 Johnnv Rock1949-10-20 iohnr @mail.com 151595254105418 298765432123456 Waters 1999-04-23 lolawaters@mail.com 56 Red St. Baltimore MD 414392845733378 Joe 488806912544440 512651122522845 RachelSac FName LName DOB Email Address 22 Yellow Ct. Hvattsville MDULL NULL NULL 74446382 Julia inoot2929@mail.com 2 Purole Rd. Baltimore MD 35 Pink St. Eldersbura MD 726 Green Rd. Eldersbura MD Noot1989-03-10 1980-04-25 iomev14@mail.com Mever Mason1994-11-12 Alexis alexmavvv09@mail.com10 Red Ave. Baltimore MD NULL 1959-05-14 rsac837@mail.com 98371920Explanation / Answer
Please find the Trigger Fragment below:-
BEGIN
V_COUNT NUMBER;
SELECT COUNT(FINES.IDFINES) INTO V_COUNT FROM FINES JOIN CUSTOMER ON FINES.IDFINES = CUSTOMER.FINES_IDFINES JOIN CHECKOUT ON NEW.CUSTOMER_IDCUSTOMER = CUSTOMER.IDCUSTOMER;
IF (V_COUNT > 1) THEN
........
Please let me know in case of any clarifications required. Thanks!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.