Consider the COMPANY database described in Figure 3.6. Using the syntax of Oracle triggers, write active rules to do the following:

Question

(a) Whenever an employee’s project assignments are changed, check if the total hours per week spent on the employee’s projects are less than 30 or greater than 40; if so, notify the employee’s direct supervisor.

(b) Whenever an EMPLOYEE is deleted, delete the PROJECT tuples and DEPENDENT tuples related to that employee, and if the employee is managing a department or supervising any employees, set the MGRSSN for that department to null and set the SUPERSSN for those employees to null.

Answer 1

(a) We assume that a procedure TELL_SUPERVISOR(ARGSSN) has been created. This procedure looks for an employee whose SSN matches the procedure’s AGRSSN argument and it notifies the supervisor of that employee.
CREATE TRIGGER INFORM_SUPERVISOR_ABOUT_HOURS
AFTER UPDATE OF HOURS ON WORKS_ON
FOR EACH ROW
WHEN ((SELECT SUM(HOURS)
FROM WORKS_ON
WHERE ESSN = NEW.ESSN) < 30
OR
(SELECT SUM(HOURS)
FROM WORKS_ON
WHERE ESSN = NEW.ESSN) > 40)
TELL_SUPERVISOR (NEW.ESSN);

(b) CREATE TRIGGER DELETE_IN_CASCADE
AFTER DELETE ON EMPLOYEE
FOR EACH ROW
BEGIN
DELETE FROM WORKS_ON
WHERE ESSN = OLD.SSN;
DELETE FROM DEPENDENT
WHERE ESSN = OLD.SSN;
UPDATE EMPLOYE
SET SUPERSSN = ‘null’
WHERE SUPERSSN = OLD.SSN;
END;