Databases, MySQL:

CUSTOMER(CustomerID, FirstName, LastName, Street, City, State, Zip, Phone)

RENTAL(RentalID, RentalDate,NumberOfDays, RentalAmount, Deposit, Tax, TotalAmount, CustomerID, ReturnDate, AmountReturned)

LINEITEM(RentalID, CostumeID, Rate, Deposit)

COSTUMETYPE(TypeID, Description, Photo, DailyRentalRate, DepositRate, ReplacementCost)

COSTUME(CostumeID, Size, Availability, DatePurchased, CostumeType)

Suppose that there is a rule that a costumes record should be deleted only if the costume is not currently being rented. Thus, if Costume has an availability of 0, and has a related LineItem record on a Rental where the ReturnDate is null, then should not have been deleted. They use a trigger to handle the deletions. MySQL does not allow the trigger to undo a deletion. However, the trigger can record information in the log file, that can be verified by the user. Explain how you would use this trigger to enforce this rule.

Show the SQL statements that you would code, and show the code for the trigger, as well as a test for the trigger.