Notes

--QUESTION TWO

--DROP
DROP FUNCTION func_calc_rem_time;
DROP PROCEDURE proc_calc_rem_time;
DROP TRIGGER trig_update_staff;

--creating a function to calculate the remaining time at the company
CREATE OR REPLACE FUNCTION func_calc_rem_time(in_date_of_birth staff.date_of_birth%TYPE)
RETURN NUMBER IS
vn_rem_time_left NUMBER(3);
BEGIN
vn_rem_time_left := (67 - func_calc_age(in_date_of_birth));

RETURN vn_rem_time_left;

END func_calc_rem_time;
/
SHOW ERRORS;

--Testing of a function(creating procedure for that)
CREATE OR REPLACE PROCEDURE proc_calc_rem_time(in_date_of_birth staff.date_of_birth%TYPE) IS
vn_rem_time_left NUMBER(3);
BEGIN
vn_rem_time_left := func_calc_rem_time(in_date_of_birth);
DBMS_OUTPUT.PUT_LINE ('Your remaining time in the company is ' || vn_rem_time_left || ' years. ');
END proc_calc_rem_time;
/
SHOW ERRORS;

EXEC proc_calc_rem_time('02-MAR-1989');
--function is correct.. Your remaining time in the company is 37 years is displayed

----to display the remaining time automatically using trigger while updating
CREATE OR REPLACE TRIGGER trig_update_staff
AFTER UPDATE ON staff
FOR EACH ROW

DECLARE
vn_rem_time_left NUMBER(3);
BEGIN
vn_rem_time_left := func_calc_rem_time(:NEW.date_of_birth);
DBMS_OUTPUT.PUT_LINE ('You are updating ' || :NEW.firstname || ' and Your remaining time in the company is ' || vn_rem_time_left || ' years.' );

END trig_update_staff;
/
SHOW ERRORS;

--Testing
/*
SELECT * FROM staff;
--1 rows selected

UPDATE staff SET firstname='ROSHAN'
WHERE staff_id = 1000;
--you are updating roshan and your remaining time in the company is 13 years has been displayed
*/