Write trigger - nomgr - which checks for the following when an INSERT, UPDATE or DELETE takes
Question:
Write trigger - nomgr - which checks for the following when an INSERT, UPDATE or DELETE takes place that ensures every employee has a manager.
Create EMPAUDIT table.
Start by INSERTing a HR Manager into the EMPLOYEE table which works for WORKDEPT = "000". This will be a default HR Manager for employees being INSERTed to a department which does not have a manager.
The trigger should be executed when any I/U/D takes place against the EMPLOYEE table which changes the WORKDEPT.
For an INSERT or UPDATE - if there is a manager for the WORKDEPT - there is nothing to do. If there is not a manager update the record so that the WORKDEPT is "000".
A record should be recorded in the EMPAUDIT table to reflect that the the WORKDEPT had to change. The audit record should record the original desired department and an error code which makes the error type clear (see details below).
When a DELETE takes place and deletes a manager (JOB="MANAGER") all the employees currently in that department need to shift to WORKDEPT "000". Note that this could impact multiple employees and you'll need a loop to deal with that.
EMPAUDIT Table Schema:
- EMPID
- ERRORCODE ("S", "B", "C", "M")
o "S" = Sum rule broken
o "B" = Bonus rule broken
o "C" = Commission rule broken
o "M" = Department had no manager
- OPERATION ("I", "U" or "D")
o "I" = INSERT Operation
o "U" = UPDATE Operation
o "D" = DELETE Operation
- WORKDEPT
o Rejected department number, if error is "M"
o "N/A", if error is "S", "B" or "C"
- SALARY
- COMM
- BONUS
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last