Lotus

Lotus

  • NA
  • 28
  • 26.5k

PL SQL trigger to restrict employee in department

Nov 22 2013 12:16 AM
i am making a trigger in plsql to restrict the employees after reaching define limit  but it give me error - ORA-01403: no data found on my form.anyone help


create or replace trigger DEPT_STRENTH
  before insert or update on empmasterinfo
  for each row
DECLARE

  -- local variables here
  EMP_Count    NUMBER;
  MAX_Strength NUMBER;

BEGIN

  select d.strength
    into MAX_Strength
    from dept_strength d
   where d.Mainid = :new.Mainid
     and d.orgelementname = :new.orgelementname;
  select count(e.employeeid)
    into EMP_Count
    from empmasterinfo e
   where e.emp_status = 0
     and e.Mainid = :new.mainid
     and e.orgelementname = :new.orgelementname;
  if MAX_Strength <= EMP_Count then
  
    RAISE_APPLICATION_ERROR(-20101,
                            'Maximum Number of Employees in Department Reached');
  else
    null;
  end if;

end DEPT_STRENTH;


Answers (1)