ORACLE PL/SQL: my SELECT COUNT statement isn't working for triggers

Question

So I want to create a trigger that fires an error message if the number of records have exceeded 16 in the table. I tried using the following code but nothing happened, I am still able to insert in more records after 16.

create or replace trigger trg_ins_jim before insert on JIM for each row
declare
     v_id_end RESULTS.id%type;
     v_prices RESULTS.PRICE%type;
     v_count NUMBER(20);
begin
   v_prices := :new.age + :new.price;
   SELECT COUNT(*) INTO v_count FROM JIM;
   IF INSERTING AND v_count<=16 THEN
   insert into RESULTS(ID,PRICE) values(:NEW.ID,v_prices);
   ELSIF v_count>16 THEN 
   Raise_Application_Error (-20343, 'Too many records');
   END IF;
end;

I think there is a problem with the way the select statement is set because when I changed v_count to :new.ID just to check if the same problem retains, I was able to get an error message if the ID number was greater than 16

Answer 1

This is not an issue triggers are good for. Let's observe what happens with various types of DML Triggers.

create table jim (id number, age number, price number);
create table results (id number, price number);
--------------------------------------------------------------
create or replace trigger trg_ins_jim_bir --Before insert row
   before insert on jim 
   for each row
declare
     v_id_end results.id%type;
     v_prices results.price%type;
     v_count number(20);
begin
   v_prices := :new.age + :new.price;
   select count(*) into v_count from jim;
   if v_count<=16 then
        insert into results(id,price) values(:new.id,v_prices);
   else
       raise_application_error (-20343, 'Too many records');
   end if;
end;
/
alter trigger trg_ins_jim_bir  disable; 
/
--------------------------------------------------------------
create or replace trigger trg_ins_jim_birat --Before insert row autonomous transaction
   before insert on jim 
   for each row
declare
     PRAGMA AUTONOMOUS_TRANSACTION;
     v_prices results.price%type;
     v_count number(20);
begin
   v_prices := :new.age + :new.price;
   select count(*) into v_count from jim;
   if v_count<=16 then
        insert into results(id,price) values(:new.id,v_prices);
        commit; 
   else
       raise_application_error (-20344, 'Too many records');
   end if;
end;
/
alter trigger trg_ins_jim_birat  disable; 
/

--------------------------------------------------------------
create or replace trigger trg_ins_jim_air --After insert row
   after insert on jim 
   for each row
declare
     v_id_end results.id%type;
     v_prices results.price%type;
     v_count number(20);
begin
   v_prices := :new.age + :new.price;
   select count(*) into v_count from jim;
   if v_count<=16 then
        insert into results(id,price) values(:new.id,v_prices);
   else
       raise_application_error (-20345, 'Too many records');
   end if;
end;
/
alter trigger trg_ins_jim_air  disable;
/
--------------------------------------------------------------
create or replace trigger trg_ins_jim_airat --After insert row autonomous transaction
   after insert on jim 
   for each row
declare
     PRAGMA AUTONOMOUS_TRANSACTION;
     v_prices results.price%type;
     v_count number(20);
begin
   v_prices := :new.age + :new.price;
   select count(*) into v_count from jim;
   if v_count<=16 then
        insert into results(id,price) values(:new.id,v_prices);
        commit; 
   else
       raise_application_error (-20346, 'Too many records');
   end if;
end;
/
alter trigger trg_ins_jim_airat  disable; 
/
--------------------------------------------------------------
create or replace trigger trg_ins_jim_ais -- After insert statement
  after insert on jim 
declare
     v_count number(20);
begin
   select count(*) into v_count from jim;
   if v_count>16 then 
       raise_application_error (-20347, 'Too many records');
   end if;
end;
/
alter trigger trg_ins_jim_ais  disable;
/
--------------------------------------------------------------
-- Following run in sqldeveloper
alter trigger trg_ins_jim_bir enable;
insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15;

/*
insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15
Error report -
ORA-04091: table BOB.JIM is mutating, trigger/function may not see it
ORA-06512: at "BOB.TRG_INS_JIM_BIR", line 7
ORA-04088: error during execution of trigger 'BOB.TRG_INS_JIM_BIR'

This is because a row level trigger CONNOT refer to the table that caused it to fire.
*/ 
select 'Jim', count(*) cnt from jim
union all 
select 'Results', count(*) from results;

/*
Jim 0
Results 0
 OK Nothing inserted. 
*/

alter trigger trg_ins_jim_bir disable; 
alter trigger trg_ins_jim_air enable;
insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15;
/*
insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15
Error report -
ORA-04091: table BOB.JIM is mutating, trigger/function may not see it
ORA-06512: at "BOB.TRG_INS_JIM_AIR", line 7
ORA-04088: error during execution of trigger 'BOB.TRG_INS_JIM_AIR'

Same result a row level trigger CONNOT refer to the table that caused it to fire
*/

--- How about an AUTONOMOUS_TRANSACTION;

alter trigger trg_ins_jim_air disable; 
alter trigger trg_ins_jim_birat enable;

insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15;
-- 14 rows inserted
select 'Jim', count(*) cnt from jim
union all 
select 'Results', count(*) from results;
/*  
Jim 14
Results 14
(looks promising) so continue
*/ 
commit ; 

insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15;
-- 14 rows inserted

select 'Jim', count(*) cnt from jim
union all 
select 'Results', count(*) from results;
/*
Jim 28
Results 28
  NO that allows 28 rows in table. 
  Well not because the trigger isn't working but because it's an AUTONOMOUS TRANSACTION. (which cannot see the rows processed outside of it)
  But is it because we used a BEFORE Trigger?
*/
rollback ; 
select 'Jim', count(*) cnt from jim
union all 
select 'Results', count(*) from results;
/*
Jim  14
Results 28

HOW?  The rollback above rolls back the second set of rows in jim, but NOT results as they were committed
during to the requirements of AUTONOMOUS TRANSACTION.
*/
delete from jim;
delete from results;
insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15;
-- 14 rows inserted
commit ; 
alter trigger trg_ins_jim_birat disable;
alter trigger trg_ins_jim_airat enable;

select 'Jim', count(*) cnt from jim
union all 
select 'Results', count(*) from results;
/*
Jim 14
Results 14 
*/
insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15;
-- 14 rows inserted 

select 'Jim' tbl, count(*) cnt from jim
union all 
select 'Results', count(*) from results; 
/*
Jim 28
Results 28

Same results for after trigger as before with AUTONOMOUS TRANSACTION.
*/ 
rollback; 
select 'Jim' tbl, count(*) cnt from jim
union all 
select 'Results', count(*) from results; 
/*
Jim 14
Results 28

And same results after rollback;
*/

delete from jim; 
delete from results; 
commit ; 

alter trigger trg_ins_jim_airat disable;
alter trigger trg_ins_jim_ais enable;

insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15;
-- 14 rows inserted
select 'Jim' tbl, count(*) cnt from jim
union all 
select 'Results', count(*) from results; 
/*
Jim 14
Results 0
OOPS: Nothing in results. 
*/
rollback;
alter trigger trg_ins_jim_airat enable;

insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15;
-- 14 rows inserted
select 'Jim' tbl, count(*) cnt from jim
union all 
select 'Results', count(*) from results; 
/* 
Jim 14
Results 14
*/ 
insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15;
/*
Error starting at line : 266 in command -
insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15
Error report -
ORA-20347: Too many records
ORA-06512: at "BOB.TRG_INS_JIM_AIS", line 6
ORA-04088: error during execution of trigger 'BOB.TRG_INS_JIM_AIS'

But is it really?
*/ 
select 'Jim' tbl, count(*) cnt from jim
union all 
select 'Results', count(*) from results; 
/* 
Jim 14
Results 28

Well maybe not!!
*/

delete from jim; 
delete from results; 
commit ; 

select 'Jim' tbl, count(*) cnt from jim
union all 
select 'Results', count(*) from results;
/* 
Jim 0
Results 0
*/ 
insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15000;
/*
insert into jim (id, age, price)
  select level, trunc(dbms_random.value(10,30)),  round(50*dbms_random.value,2)
   from dual connect by level < 15000
Error report -
ORA-20347: Too many records
ORA-06512: at "BOB.TRG_INS_JIM_AIS", line 6
ORA-04088: error during execution of trigger 'BOB.TRG_INS_JIM_AIS'
*/ 

select 'Jim' tbl, count(*) cnt from jim
union all 
select 'Results', count(*) from results;
/*
Jim 0
Results 14999

Is this really what you want?
*/ 

Moral of the story: Enforce this rule in the App one way or another, not database triggers. And everything here is a single session. It gets much more complicated with multiple sessions.

Answer 2

This is a BEFORE trigger so the new row isn't included in the SELECT COUNT(*) because it's not in the table yet.

Try checking for less than 16 instead of less than or equal to 16. Change this:

IF INSERTING AND v_count<=16 THEN

... to this:

IF INSERTING AND v_count<16 THEN