--7. RETURN A FILM --
CREATE TRIGGER ReturnAFilm
ON Receipt_Film
AFTER INSERT
AS
DECLARE @RentID INT
DECLARE @FilmID INT
SELECT @RentID = RentID, @FilmID = FilmID FROM INSERTED
DECLARE @Amount FLOAT
DECLARE @Quantity INT
DECLARE @MachineID INT
SELECT @Amount = PricePerDay * DATEDIFF(DAY, RentDate, ReturnDate), @Quantity = Quantity, @MachineID = Receipt.MachineID FROM Receipt
INNER JOIN Receipt_Film
ON Receipt_Film.ReceiptID = Receipt.ID
INNER JOIN Rent
ON Rent.ID = Receipt_Film.RentID
INNER JOIN Rent_Film
ON Rent_Film.FilmID = Receipt_Film.FilmID AND Rent_Film.RentID = Receipt_Film.RentID
INNER JOIN Film
ON Film.ID = Rent_Film.FilmID
WHERE Receipt_Film.RentID = @RentID AND Receipt_Film.FilmID = @FilmID
UPDATE Receipt_Film SET Amount = @Amount
WHERE RentID = @RentID AND FilmID = @FilmID
UPDATE Machine_Film SET NumberOfFilms = NumberOfFilms + @Quantity
WHERE MachineID = @MachineID AND FilmID = @FilmID

GO