PLSQL触发器ORA 01403未找到数据 [英] PLSQL Trigger ORA 01403 no data found

问题描述

我在PL-SQL中创建一个触发器以限制员工在我的员工输入表单上的部分/ dept
中获得ORA-01403：没有找到数据。
请任何人帮助我

 在empmasterinfo 
上插入后创建或替换触发器DEPT_STRENTH 
每行
 DECLARE 
  - 这里的局部变量
 EMP_Count NUMBER; 
 MAX_Strength NUMBER; 
 V_Mainid VARCHAR2（100）; 
 V_orgelementname VARCHAR2（100）; 
 BEGIN 
 
  - 从表单
 
获取值V_Mainid：=：new.mainid; 
 V_orgelementname：=：new.orgelementname; 
 
  - 与现有的值比较
 
选择d.strength 
到MAX_Strength 
从dept_strength d 
 
  - 主表
 
 
其中d.Mainid = V_Mainid 
和d.orgelementname = V_orgelementname; 
 
 select count（e.employeeid）
 into EMP_Count 
 
  - 主表
 
 
来自empmasterinfo e 
其中e.emp_status = 0 
和e.Mainid = V_Mainid 
和e.orgelementname = V_orgelementname; 
 
如果EMP_Count> = MAX_Strength然后
 
 RAISE_APPLICATION_ERROR（-20101，
'到达部门的最大员工人数'）; 
 
 end if; 
 
 end DEPT_STRENTH; 
  

解决方案

。

第一步是查看你写的内容。 NO_DATA_FOUND异常由不返回任何行的查询抛出。您的触发器中有两个查询。但是聚合查询不会引发异常，因为计数将返回0.

因此，只有一个查询可以抛弃ORA-01403，这清楚地表明你不在 dept_strength 中与 empmasterinfo 中插入的行匹配任何行。可能你认为你应该在该表中有行，在这种情况下，你需要重新审视你的事务逻辑。

你应该这样做，试图强制这种排序的业务规则在触发器是一个坏的错误。它不扩展，它不能在多用户环境中工作。

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;

解决方案

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.

这篇关于PLSQL触发器ORA 01403未找到数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！