oracle - Trigger to enforce M-M relationship

Question

Suppose I have following schema :

DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, Phone)
EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email)

The problem am facing is how to design a system of triggers to enforce the M-M relationship.Assuming that departments with only one employee can be deleted. Also I need to assign the last employee in a department to Human Resources.

I have no idea to enforce M-M relationship through trigger. Please help

Answer 1

Many-to-many conditions should not be enforced using a trigger. Many-to-many conditions are enforced by creating a junction table containing the keys in question, which are then foreign-keyed back to the respective parent tables.

If your intention is to allow many employees to be in a department, and to allow an employee to be a member of many departments, the junction table in question would look something like:

CREATE TABLE EMPLOYEES_DEPARTMENTS
  (DEPARTMENTNAME  VARCHAR2(99)
     CONSTRAINT EMPLOYEES_DEPARTMENTS_FK1
       REFERENCES DEPARTMENT.DEPARTMENTNAME,
   EMPLOYEENUMBER  NUMBER
     CONSTRAINT EMPLOYEES_DEPARTMENTS_FK2
       REFERENCES EMPLOYEE.EMPLOYEENUMBER);

This presumes that DEPARTMENT.DEPARTMENTNAME and EMPLOYEE.EMPLOYEENUMBER are either primary or unique keys on their respective tables. Get rid of the column EMPLOYEE.DEPARTMENT as it's no longer needed. Now by creating rows in the EMPLOYEES_DEPARTMENTS table you can relate multiple employees with a department, and you can relate a single employee with multiple departments.

The business logic requiring that only departments with one or fewer employees can be deleted should not be enforced in a trigger. Business logic should be performed by application code, NEVER by triggers. Putting business logic in triggers is a gate?w?ay to unending debugging sessions. M??????a???d?????n???e???????ss??? lies this way. Do not give in. Do not surrender. ??B?usi?n?es?s logic ?in triggers opens deep wounds in the fabric of the world, through which unholy beings of indeterminate form will cross the barrier between the spheres, carryi?n?g o?f?f t?h?e ?screaming? sou?l?s o?f? ?th?e? ?de?v?e?lop?e?r?s to an et?er?n?it?y ?of? pain? a?n?d? ?to?r?ment?. Do not, as I have said, put b?u?siness? ?log?i?c? ?in? ?trigge?rs?.? Be firm. Resist.You must resist. T?he ?Tem?p?t?at?i?o?n?s? ?m?a?y ?s?ing hymns? ?o?f? ?un?hol?y r?ev?e?la?t?ion? ?but? ?yo?u? mu?s?t ?n???o?t??? ?l????i?????s????t???e??n?????. Only by standing firmly in the door between the worlds and blocking out the hideous radiance cast off by bu??s?i??n??e?s??s ?l?o??g??i??c i?n? ?t??r??i???g??ge???r?s??, which perverts the very form of the world a?nd??? c??a??l?l??s??? Z??????????????????A?????????????????L??????????G???????????O????????????? i??nt?o?? ?b??e??i??n?g?,??? ?c?a???n? ??your?? ??a?p???pl??ic?a?t?i??o?n? ?s??u??r?v?i??v?e?.?? Resist. R???e???s???i???s?t??. T??h??e?r??e?? ?i???s? ??n??o? ?w???a?y?? ?b??u???t? ?s??t??i??l?l?? ???y?o??u?? ??mus??t??? ?re??s??i??s?t?.??? Your very s?o?u???l??? is compromised by p?u??t??t?i?n???g?? ???b??u?????s?????i????n??e??s???s????? ??l?????o??g?i??c?? ??i???n??? ???t?????r?????i?????g????g????e????r??s?????. T???o??ny? ??T?h???e ??P?o??n?y shall rise from his dark stable and d?e??v??o??u??r?? ?t?h?e? ??s?o??u?l? ??o??f?? ?t?h??e?? ??vi??r??g?i?n?, and yet y?o?u?? m??u?s?t?? ?s??t??i??l??l ?n?ot? ?p?u?t? ??b??u?s?i??n???e?s??s ??l??o?g?i??c ??i?nt?o? ???t??r??i?g??g???e???r??s?! It is too much to bear, we cannot stand! Not even the children of light may put business logic into their triggers, for b????u????s??i?????n?????e????s???s?? ????l??o????g???i?????c??? ?????i????n?? ???t????r?i????g???g??e???r?????s??? is the very es?s??e??n?c?e?? ?o???f? ?d?ar???k??n??es???s??? and dev?ou??r???s?? ??t?h?e?? l??i???g????????h?????????t????! Yea, yea, the blank-faced ones ri??s??e from the f???o???n?t? ?o?f??? ??fl??a???me??? and ca?s?t? down the p????r?????????????i?????e????????s??????t???s??? ???????f????????r??????o???????m?????? ???????t?????h??????e????????????i??????????r???? ?a?????l????????????t????a??????????r????????s???????! A???v?e??r?t?? ?y???o?u??r?? ?e?y?e??s,? ?t?h?o??ug??h?? ?y?o???u?? ??c???an??n?o??t!??? ?H?e??? ??c?o?m???e??s??!?? ?H?e? ?c???o??m??es?!??? P?????h?????'??????n?????g???????l???????u???????i???????? ????m?g?????l????w????'????n??a?f???????h????? ??????C?????t????h??u?????l??hu????? ??R??'?????l????????y??e????h?????? w???????g????a??h????'????n?????a????g???l?? ????????f??h?????t???a??g?????n??????!

Don't ask me how I know.

Best of luck.