Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

i am making a trigger in PL-SQL to restrict employees in section/dept on my employee entry form i get ORA-01403: no data found . please anyone help me

create or replace trigger DEPT_STRENTH
  after insert on empmasterinfo
  for each row
DECLARE
  -- local variables here
  EMP_Count        NUMBER;
  MAX_Strength     NUMBER;
  V_Mainid         VARCHAR2(100);
  V_orgelementname VARCHAR2(100);
BEGIN

--taking value from form

 V_Mainid         := :new.mainid;
  V_orgelementname := :new.orgelementname;

--Comparing values with existing 

  select d.strength
    into MAX_Strength
    from dept_strength d 

-- Master table 


 where d.Mainid = V_Mainid
     and d.orgelementname = V_orgelementname;

  select count(e.employeeid)
    into EMP_Count

-- Master table 


 from empmasterinfo e 
   where e.emp_status = 0
     and e.Mainid = V_Mainid
     and e.orgelementname = V_orgelementname;

  if EMP_Count >= MAX_Strength then

    RAISE_APPLICATION_ERROR(-20101,
                            'Maximum Number of Employees in Department Reached');

  end if;

end DEPT_STRENTH;
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
272 views
Welcome To Ask or Share your Answers For Others

1 Answer

This is an exercise in debugging your code.

Step one is to look at what you have written. The NO_DATA_FOUND exception is thrown by a query which returns no rows. You have two queries in your trigger. However aggregation queries don't raise that exception, as the count will return 0.

So only one query can be hurling ORA-01403, which clearly indicates you don't have any rows in dept_strength that match the rows you're inserting in empmasterinfo. Presumably you think you should have rows in that table, in which case you need to revisit your transactional logic.

You should probably do that anyway, as trying to enforce this sort of business rule in a trigger is a bad mistake. It doesn't scale and it doesn't work in multi-user environments.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...