I have these tables:

• Person (id_pers, name, phone, address, birthdate)
• Book (book_id, title, nr_of_pages, copies, genre)
• Loan(book_id, loan_id, dateL, dateR, nr_of_days)

birthdate is of datatype DATE;
loan_id takes value from the id_pers;
dateL - loan date;
dateR - return date;
nr_of_days represents the return term (how many days the loan may take);

Define a trigger for:
Ensure that when adding a loan, the nr_of_days is proportionate to nr_of_pages taking into account the age of the person making the loan. Between 18-25 years, 30 pages/day; between 26-65 years, 20 pages/day; over 65 years, 40 pages/day.

I tried using this trigger but it compiles with errors

create or replace TRIGGER Update_Days
BEFORE INSERT ON Loan
FOR EACH ROW
BEGIN
    DECLARE age INT;
    SELECT DATEDIFF(YEAR, birthdate, CURRENT_DATE) INTO age
    FROM Person
    WHERE id_pers = NEW.loan_id;

    IF age BETWEEN 18 AND 25 THEN
        SET NEW.nr_of_days = NEW.nr_of_pages / 30;
    ELSEIF age BETWEEN 26 AND 65 THEN
        SET NEW.nr_of_days = NEW.nr_of_pages / 20;
    ELSE
        SET NEW.nr_of_days = NEW.nr_of_pages / 40;
    END IF;
END;

errors i get

CodePudding user response：

• SET is not used in PL/SQL
• In PL/SQL, the assignment operator is := and not =
• The NEW record is referenced using bind variable syntax of :NEW
• DATEDIFF is not a valid function in Oracle.
• The DECLARE section goes before BEGIN.

CREATE OR REPLACE TRIGGER Update_Days
  BEFORE INSERT ON loan
  FOR EACH ROW
DECLARE
  age INT;
BEGIN
  SELECT TRUNC(MONTHS_BETWEEN(CURRENT_DATE, birthdate)/12)
  INTO   age
  FROM   Person
  WHERE  id_pers = :NEW.loan_id;

  :NEW.nr_of_days := :NEW.nr_of_pages / CASE
                                        WHEN age BETWEEN 18 AND 25 THEN 30
                                        WHEN age BETWEEN 26 AND 65 THEN 20
                                        ELSE 40
                                        END;
END;
/

fiddle