SQL CODE - To perform a Trigger that works monitors the status of the Supervisor. It should work on every update or delete or even insert. If the location supervisor changes, all employees in the location should reflect the change. If the location becomes for whatever reason without a supervisor, the oldest employee in the location becomes a supervisor.

CREATE TABLE PR_LOCATION ( LOCATION_ID NUMBER NOT NULL, NAME VARCHAR(100) NOT NULL UNIQUE, ADDRESS VARCHAR(200) NOT NULL, MANAGER_ID NUMBER, PRIMARY KEY (LOCATION_ID) );

--MODIFY THE PR_LOCATIONS TABLE --LATER , AFTER WE CREATE THE EMPLOYEES TABLE -- FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID) ALTER TABLE PR_LOCATION ADD CONSTRAINT MAN_FK FOREIGN KEY (MANAGER_ID) REFERENCES PR_EMPLOYEE(EMPLOYEE_ID); --UPDATE THE LOCATIONS MANAGERS UPDATE PR_LOCATION SET MANAGER_ID = 1 WHERE LOCATION_ID=1; UPDATE PR_LOCATION SET MANAGER_ID = 2 WHERE LOCATION_ID=2; UPDATE PR_LOCATION SET MANAGER_ID = 3 WHERE LOCATION_ID=3

CREATE TABLE PR_EMPLOYEE ( EMPLOYEE_ID NUMBER GENERATED ALWAYS AS IDENTITY, NAME VARCHAR(100) NOT NULL, DOB DATE NOT NULL, WAGE_HOUR NUMERIC(10,2) NOT NULL CHECK (WAGE_HOUR >= 10), GENDER CHAR(1) NOT NULL, LOCATION_ID NUMBER NOT NULL, SUPERVISOR_ID NUMBER, PRIMARY KEY (EMPLOYEE_ID), FOREIGN KEY (LOCATION_ID) REFERENCES PR_LOCATION(LOCATION_ID), FOREIGN KEY (SUPERVISOR_ID) REFERENCES PR_EMPLOYEE(EMPLOYEE_ID) );

--ADD SUPEVISORS INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('HASSAN ALI', '17-JAN-1993', 34.5, 'M',1,NULL); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('MIKE PENCE', '12-JUL-1983', 54.5, 'M',2,NULL); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('LEO MESSI', '10-OCT-1987', 44.5, 'M',3,NULL);

--ADD EMPLOYEES INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('AHMED KAMAL', '22-JUL-1993', 66.5, 'M',1,1); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('SALAM NASSER', '15-JUL-1983', 45.5, 'M',2,2); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('MONA BELAL', '30-APR-1987', 30.5, 'M',3,3); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('ASHRAF ALI', '10-JAN-1993', 25.5, 'M',1,1); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('SALMA MAHMOUD', '12-DEC-1983', 25.5, 'M',2,2); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('NOORA HASSAN', '11-MAR-1987', 44.5, 'M',3,3); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('MAJED ALI', '24-JAN-1993', 40.5, 'M',1,1); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('SAMEER HASSAN', '12-AUG-1983', 54.5, 'M',2,2); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('MOHD JAMAL', '10-MAY-1987', 40.5, 'M',3,3); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('ALI ALI', '24-MAY-1988', 40.5, 'M',1,1); INSERT INTO PR_EMPLOYEE (NAME, DOB, WAGE_HOUR, GENDER, LOCATION_ID, SUPERVISOR_ID) VALUES ('KAMEL MOHD', '12-MAY-1993', 30.5, 'M',2,2); SELECT * FROM PR_EMPLOYEE;