This content has been marked as final. Show 1 reply
Perhaps you're misremembering because this is not generally an easy thing to do in a trigger.1 person found this helpful
In the vast majority of cases, you would do this by creating a stored procedure that assigns jobs to employees and enforcing the logic in that stored procedure. You would then prevent users from issuing INSERT and UPDATE commands directly against the table, requiring them to go through the stored procedure. This isn't completely foolproof unless you serialize access by doing something like locking the employee row-- otherwise, two users in two different sessions could both add a job for an employee that already has 4, both would pass the check, and both sessions could commit, leaving the employee with 6 jobs.
If you don't care about performance, you could have an after statement trigger that looked for any employee with more than 5 jobs and raised an exception. This has the same race condition problem that the procedure has but it's harder to work around in a trigger. It's also quite inefficient since it involves scanning the entire table every time you insert a row.
You can create a materialized view that is set to refresh on commit that stores the employee ID and the number of jobs and then create a constraint on the materialized view that ensures that the number of jobs is never more than 5. Since the refresh happens at commit time, you don't have to deal with serializing access. But you would have to create a materialized view log and a materialized view that would store some redundant data. And you'd have to ensure that your applications were designed with the ability to handle the fact that a COMMIT might fail (and may take some time) since that's the point that the constraint would be enforced.
You could also go with a three trigger solution (or a compound trigger with three parts in 11g). You'd create a package with a collection of employee numbers. A before statement trigger would initialize the collection. A row-level trigger would insert the :new.emp_no into the packaged collection. And then an after statement trigger would iterate over the elements of the collection and check the table to see whether there were more than 5 jobs for that employee. Again, this has the same race condition in a multi-user system and it's still hard to work around that in order to serialize access. It's more efficient than the simpler trigger solution but it's also more work.