trigger: 1: CREATE OR REPLACE FUNCTION UpdateLastRentalDate() RETURNS… — Database 4031 — TG.ME

CREATE OR REPLACE FUNCTION UpdateLastRentalDate()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE customer
    SET last_rental_date = NEW.rental_date
    WHERE customer_id = NEW.customer_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER UpdateCustomerLastRentalDate
AFTER INSERT ON rental
FOR EACH ROW
EXECUTE FUNCTION UpdateLastRentalDate();

CREATE OR REPLACE FUNCTION CheckInventoryAvailability()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM rental
        WHERE inventory_id = NEW.inventory_id
        AND return_date IS NULL
    ) THEN
        RAISE EXCEPTION 'This inventory item is already rented out and has not been returned.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER PreventDoubleRental
BEFORE INSERT ON rental
FOR EACH ROW
EXECUTE FUNCTION CheckInventoryAvailability();

-- First, create the trigger function
CREATE OR REPLACE FUNCTION CheckFilmReleaseYear()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the release year is less than 1900
    IF NEW.release_year < 1900 THEN
        -- Raise an exception to prevent insertion
        RAISE EXCEPTION 'Release year cannot be less than 1900.';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Now, create the trigger to call the function before inserting a new film
CREATE TRIGGER ValidateReleaseYear
BEFORE INSERT ON film
FOR EACH ROW
EXECUTE FUNCTION CheckFilmReleaseYear();