This discussion is archived
1 Reply Latest reply: Feb 22, 2013 1:18 PM by 992858 RSS

Setting up a trigger

992858 Newbie
Currently Being Moderated
Hi

Currently creating a application as part of a University project and my mind is literally gone blank, I have done this in the past!

Basically I am trying to create a trigger so that if someone trys to assign more than 5 jobs to an employee then the trigger will stop them.

I have got so far with the following simple trigger which basically allows five jobs to be assigned then the trigger blocks anymore jobs being assigned, which is what I want to happen but for an employee not the whole lot! Here is what I have created so far:

create or replace TRIGGER max_jobs_ed
BEFORE INSERT OR UPDATE ON CNEP_ISSUE
DECLARE
NO_JOBS NUMBER;
BEGIN
SELECT COUNT ("CNEP_ISSUE"."EMP_NO") INTO NO_JOBS
from "CNEP_ISSUE" ;
IF NO_JOBS > 5 THEN
RAISE_APPLICATION_ERROR(-20999, 'TEST');
END IF;
END;​​

The table I'm using/refering to is:

CNEP_ISSUE
ISSUE_NO
ISSUE_RESPONSE
ISSUE_STATUS_ID
EMP_NO
COMPLETION_DATE     

I know this is easy I have just generally forgotton how I have done it before.

Thanks in advance for any help with this.
  • 1. Re: Setting up a trigger
    Justin Cave Oracle ACE
    Currently Being Moderated
    Perhaps you're misremembering because this is not generally an easy thing to do in a trigger.

    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.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points