ORA-01403: no data found from INSERT trigger

Question

I'm trying to configure a database using oracle 11g release 2 where I created a table named UTILITY:

  create table utility
  (
    u_id varchar2(12),
    name varchar2(20)NOT NULL,
    status varchar2(10),
    paid_amount number(7,2)NOT NULL,
    month varchar2 (2) NOT NULL,
    year varchar2 (4) NOT NULL,
    company varchar2(20)NOT NULL,
    constraint u_id_pk primary key(u_id)
 );

I also created a sequence

CREATE SEQUENCE  "ASMADB"."UTILITY_ID_SEQ"  MINVALUE 1 MAXVALUE 9999999999 
INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE ;

and a trigger for the table:

create or replace trigger uility_id
before insert on utility
for each row
begin
    select to_char('U')||'-'||lpad(utility_id_seq.nextval,9,'0')
    into :new.u_id  
    from utility;
end;

but whenever I try to insert values in utility following error occurs:

Error starting at line : 1 in command
insert into utility values (' ','Electricity Bill','due',5340.59,to_char(to_date('12/06/2020','dd/mm/yyyy'),'mm'),to_char(to_date('12/06/2020','dd/mm/yyyy'),'yyyy'),'Rakib Electricity')
Error report
ORA-01403: no data found
ORA-06512: at "ASMADB.UILITY_ID", line 2
ORA-04088: error during execution of trigger 'ASMADB.UILITY_ID'

It will be very helpful if someone helps me to resolve this error.

Note: the table Utility is empty.

Well, your trigger is selecting from a table that you yourself said is empty. Isn't it obvious what is causing the error? Why are you even bothering to SELECT from the empty table, when you are not actually selecting any data from it? side observation - you should not have separate columns for 'year' and 'month', but one column of type DATE. If you don't care about the day of month, but just year and month then default the 'dd' to '01'. — EdStevens
Another side observation. None of your current column names are actually reserved words, but I had to confirm that. It is a good habit to always name your columns in the format <adjective>_<noun>. That way you never have to worry about accidentally using a reserved or key word, and your code will be much more 'self-documenting'. — EdStevens
The select into would only work if utility had exactly one row. But in general you can't query the triggering table inside a row-level trigger on that table as the data is mutating, meaning the final values of that row are not yet resolved, and in any case there could be other sessions doing the same thing. Why are you querying it? — William Robertson

Littlefoot Littlefoot · Accepted Answer · 2021-01-01T18:46:31

Not like that; simply

create or replace trigger uility_id
  before insert on utility
  for each row
begin
  :new.u_id := 'U-' || lpad(utility_id_seq.nextval,9,'0');
end;

ORA-01403: no data found from INSERT trigger

1 Answers