sqldatabaseoracle-databasetriggersoracle11gr2

ORA-01403: no data found from INSERT trigger

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.

Solution

  • 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;