PLSQL Statement Trigger Firing Twice

Question

I have a task to do where I have to implement an error trigger using a statement trigger in oracle SQL Developer. My table of data currently looks like this:

ANUMBER	EXTRACT(YEARFROMAPPDATE)	APPCOUNT
2	1999	2
4	2000	2

Essentially, the data counts the amount of applications an applicant makes per year. The job of the trigger statement is to verify a consistency constraint that an applicant can have no more than three applications per year.

Here is my trigger statement:

CREATE OR REPLACE TRIGGER application_count_constraint
--checks insert/update before 
BEFORE UPDATE OR INSERT ON APPLIES
BEGIN
  
--loops through a select statement which shows how many applications each 
--person has per year
FOR x IN (SELECT anumber, 
                  extract(year FROM APPDATE), 
                  count(extract(year FROM APPDATE)) AS appcount
                  FROM APPLIES 
                  GROUP BY anumber, extract(year FROM APPDATE)
                  ORDER BY COUNT(EXTRACT(YEAR FROM APPDATE)) DESC)
      LOOP 
          --counts if the value is going to be greater than 3
          IF (x.appcount + 1) > 3 THEN
            --raises application error (good error)
            RAISE_APPLICATION_ERROR(-20001, 'An applicant can not have more than 3 applications');
            EXIT;
          END IF;
      END LOOP;
END;
/ 

So to test the trigger, I simply add two applications for anumber 2. The first insert works fine because it hasn't violated > 3 applications per year.

The second insert does violate the consistency constraint and the trigger fires correctly. Here however is my question.

Here's the error I'm getting when the trigger fires:

Error starting at line : 58 in command -
INSERT INTO APPLIES VALUES( 000002, 00000002, TO_DATE('07-JUN-1999','DD-MON-YYYY') )
Error report -
ORA-20001: An applicant can not have more than 3 applications
ORA-06512: at "DON988.APPLICATION_COUNT_CONSTRAINT", line 15
ORA-06512: at "DON988.APPLICATION_COUNT_CONSTRAINT", line 15
ORA-04088: error during execution of trigger 'DON988.APPLICATION_COUNT_CONSTRAINT'

I would like to know why the 'ORA-06512' error happens twice. I've tried a few different things but it only ever fires twice. If anyone could explain why that'd be a massive help.

Answer 1

The trigger doesn't fire twice. I'd guess the duplicated ORA-06512 message in the error stack is due to the way the compiler structures the code internally. Maybe there is a way to do a deep trace and get to the bottom of it, but I would just ignore it.

The trigger could be simplified to something like this, avoiding a loop and thus the double 6512 error:

create or replace trigger application_count_constraint
    before update or insert on applies
declare
    l_appcount number;
begin
    -- Detect case where more than 3 applications exist
    -- (assumes there were none before the current transaction and no other sessions are inserting/updating)
    select count(*) into l_appcount
    from   applies
    group  by anumber, to_char(appdate,'YYYY') having count(*) > 3
    order by count(*) desc
    fetch first row only;

    if l_appcount > 0 then
        raise_application_error(-20001, 'An applicant can not have more than 3 applications');
    end if;
exception
    when no_data_found then null;
end;

---

Edit: As @ThorstenKettner pointed out, attempts to use a row-level trigger fail with ORA-04091: table is mutating for multi-row inserts, so the version below won't work. This is in addition to being easily circumvented by concurrent inserts in two sessions as I mention below.

---

create or replace trigger this_wont_work
    before update or insert on applies for each row
declare
    appcount number := 0;
begin
    select count(*) into appcount
    from   applies
    where  anumber = :new.anumber
    and    trunc(appdate,'YEAR') = trunc(:new.appdate,'YEAR');

    if appcount >= 3 then
        raise_application_error(-20001, 'Applicant '||:new.anumber||' already has '||appcount||' applications in year '||to_char(:new.appdate,'YYYY'));
    end if;
end;

However, two sessions could easily bypass the check if each one inserted one or two rows and then committed a little later, as the check will only see committed rows. Therefore you might need to look at serialising updates somehow (the simplest approach might be to lock table applies in exclusive mode; at the start of the trigger), or else create an on-commit materialised view that aggregates the data and has a check constraint on the total count column (a bit of an exercise and I haven't tried it).