Notes

--creating combined trigger
CREATE OR REPLACE TRIGGER trig_new_staff
AFTER INSERT OR UPDATE OR DELETE ON staff
FOR EACH ROW

DECLARE
vn_age1 NUMBER(3);

BEGIN
IF INSERTING THEN

vn_age1 := function_calc_age(:NEW.date_of_birth);
DBMS_OUTPUT.PUT_LINE(:NEW.firstname || ' is added with age: ' || vn_age1);

ELSIF UPDATING THEN
vn_age1 := function_age_left(:NEW.date_of_birth);
DBMS_OUTPUT.PUT_LINE(:NEW.firstname || ' has ' || vn_age1 || ' years left with the company.');

ELSE
DBMS_OUTPUT.PUT_LINE(:OLD.firstname || ' is being deleted ');


END IF;


END trig_new_staff;
/
SHOW ERRORS;


--creating procedure so staff can see thire time left
CREATE OR REPLACE PROCEDURE proc_check(in_staff_id staff.staff_id%TYPE, in_age NUMBER) IS
vd_dob DATE;
vn_age NUMBER(3);
vn_staff_age_left NUMBER(3);
BEGIN
SELECT date_of_birth
INTO vd_dob
FROM staff
WHERE staff_id = in_staff_id;

vn_age:= function_calc_age(vd_dob);

vn_staff_age_left := (in_age - vn_age);

DBMS_OUTPUT.PUT_LINE('You have ' || vn_staff_age_left || ' years before retirement' );

END proc_check;
/
SHOW ERRORS;


EXEC proc_check(1008, 100);