Trigger Applications

• Default values
  • DEFAULT value for a table is limited.
  • An insert trigger can provide more flexibility and control over default values.
• Enforcing Data Constraints
  • Assume a Optional to Mandatory relationship.
  • IE A department must have at least one employee.
  • 
  • If we have a department, it must have an employee.
  • Where are the problems?
    1. Inserting a Department?
    2. Changing the Department ID?
    3. Deleting A Department?
    4. Inserting an Employee?
    5. Assigning an Employee to a new Department?
    6. Deleting an Employee?
  • 1 strikes me as a real problem.
    • We can't have an employee in a department that doesn't exist.
    • We can't create a department without an employee.
    • We could search for an arbitrary unassigned employee and give them a department.
    • But this would seem strange. Congratulations, you have been moved from independent to the new foot care department.
    • We could assign a "default" employee to the department.
    • This would be bad too, unless we instantly had something that went through and fixed it. Otherwise, we would defeat the purpose of the O-M requirement.
    • But we could do either of these with a trigger,
    • Or have the trigger fail the insertion if we could not do it.
  • We can handle 2 and 3 with a cascading option
  • 4 is a non-issue, we can have employees without departments.
  • 5 and 6 are the same issue as 1
    • We can use the trigger to detect if we are the only employee in a department.
    • We can stop the update/deletion if this is the case.
    • Any better solution will lead to one of the problems above.