Notes

CREATE OR REPLACE FUNCTION func_retirement_age(vd_staff_dob DATE)RETURN NUMBER IS
vn_retirement_age NUMBER(3);
BEGIN
vn_retirement_age:=(67-func_staff_age(vd_staff_dob))*365;
RETURN vn_retirement_age;
END func_retirement_age;
/
SHOW ERRORS;

--creating proc_retirement_age procedure
CREATE OR REPLACE PROCEDURE proc_retirement_age(vd_staff_dob DATE) IS
vn_retirement_age NUMBER(3);
BEGIN
vn_retirement_age:=func_retirement_age(vd_staff_dob);
DBMS_OUTPUT.PUT_LINE('Retirement age of a staff is'|| vn_retirement_age);
END proc_retirement_age;
/
SHOW ERRORS;

--executing proc_retirement_age
EXECUTE proc_retirement_age('02-FEB-1880');

--creating a trigger
CREATE OR REPLACE TRIGGER trig_staff_retirement_age
AFTER UPDATE ON staff
FOR EACH ROW
BEGIN
proc_retirement_age(:NEW.date_of_birth);

END trig_staff_retirement_age;
/
SHOW ERRORS;

--testing
SELECT * FROM staff;

INSERT INTO staff VALUES (455, 'ALISON','APPLE', NULL, 'HEAD','01-JAN-1990');