ORA-01403: no data found error in businesshr trigger

Question

im trying trigger on following tables oracle sqlplus

SQL> create table Employees1727
  2  (
  3  emp_id number(4) primary key,
  4  last_name varchar2(15),
  5  first_name varchar2(15),
  6  email varchar2(30),
  7  phone_no varchar2(12),
  8  hire_date date,
  9  job_id varchar2(10), 
 10  salary number(10),
 11  commission_pct number(2,2),
 12  manager_id number(4) references Employees1727(emp_id), 
 13  dept_id number(3)
 14  );

Table created.

SQL> 
SQL> 
SQL> create table Departments1727
  2  (
  3  dept_id number(4),
  4  dept_name varchar2(15),
  5  manager_id number(4) references Employees1727(emp_id), 
  6  loc_id number(4)
  7  );

Table created.

SQL> create or replace trigger businesshr
  2  before insert or update or delete on Employees1727
  3  for each row
  4  begin
  5  if to_char(sysdate, 'hh24') >= '05' AND
  6     to_char(sysdate, 'hh24') <= '18'
  7  then
  8  select * into 
  9  :new.emp_id,
 10  :new.last_name,
 11  :new.first_name,
 12  :new.email,
 13  :new.phone_no,
 14  :new.hire_date,
 15  :new.job_id, 
 16  :new.salary,
 17  :new.commission_pct,
 18  :new.manager_id, 
 19  :new.dept_id
 20  from Employees1727 ;
 21  else
 22  raise_application_error (-20000, 'Employee info may not be modified at this time!') ;
 23  end if;
 24  end businesshr;
 25  /

Trigger created.

SQL> insert into Employees1727 values(1,'Patil','Pratik','[email protected]',23994723, '12-FEB-2010',101,15000,0,1,2);
insert into Employees1727 values(1,'Patil','Pratik','[email protected]',23994723, '12-FEB-2010',101,15000,0,1,2)
        *

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.BUSINESSHR", line 5
ORA-04088: error during execution of trigger 'SCOTT.BUSINESSHR'

Your concern is still not clear...also format the code remove the line no. — Vikash Pathak

Ponder Stibbons Ponder Stibbons · Accepted Answer · 2019-06-07T12:54:12

In trigger you shouldn't select from table on which trigger is operating, because you can get mutating table error. In this case no select is needed at all, just write it as here:

create or replace trigger businesshr
  before insert or update or delete on Employees1727 
  for each row
  begin
    if to_char(sysdate, 'hh24') < '05' or '18' < to_char(sysdate, 'hh24') then
      raise_application_error (-20000, 'Employee info may not be modified at this time!') ;
    end if;
  end businesshr;

ORA-01403: no data found error in businesshr trigger

1 Answers