Notes

/* Trigger */

--@C:TempCSY2038(RM)Week17TrigPracFuct_18413711.txt

/*
NAME : Rojen
UoN ID : 18413711
MODULE : DatabaseII
DATE : 03-03-2019
Week : 17
*/

SET SERVEROUTPUT ON;

ALTER TABLE staff ADD (
role VARCHAR2(10),
date_of_birth DATE
);


-- when a new member of staff is added, automatically display their age on the screen
CREATE OR REPLACE TRIGGER trig_display_staff_age
AFTER INSERT ON staff
FOR EACH ROW

BEGIN
DBMS_OUTPUT.PUT_LINE('The age of ' || (:NEW.staff_fistname) || ' is ' || TRUNC(((SYSDATE - :NEW.date_of_birth) / 365)) || ' years old.');
END trig_display_staff_age;
/
SHOW ERRORS;


insert into staff(id, staff_fistname, date_of_birth)
values (199, 'sushant', '11-JUN-1992');


-- When an existing member of staff’s info is changed automatically display a message showing how long they have left at the company
CREATE OR REPLACE TRIGGER trig_display_staff_retire
AFTER INSERT OR UPDATE OF staff_fistname ON staff
FOR EACH ROW

BEGIN
DBMS_OUTPUT.PUT_LINE(:NEW.staff_fistname || ' has ' || TRUNC((:OLD.date_of_birth + 365 * 67) - SYSDATE)|| ' years left until they retire.');
END trig_display_staff_retire;
/
SHOW ERRORS;


insert into staff(id, staff_fistname, date_of_birth)
values (199, 'sushant', '11-JUN-1992');