时间戳

首页 > 解决方案 > Oracle 触发器在插入和更新时检查值

oracle - Oracle 触发器在插入和更新时检查值

问题描述

我正在尝试检查是否存在相互交叉的价值差距。让我用一个快速的场景来解释一下。

建议 1：AA 公司：距离差距 -> 100 - 200
建议 2：AA 公司：距离差距 -> 200 - 300 有效
建议 3：AA 公司：距离差距 -> 250 - 450 无效
建议 4：JL 公司：距离差距 - > 250 - 450 有效

Rec 3 无效，因为它在 REC 2 的距离值之间。建议 4 有效，因为公司不同

因此我写了一个触发器来防止它。

create or replace trigger ab_redemption_billing_mpr
  after insert or update on redemption_billing_mpr
  for each row
declare
  v_is_distance_range_valid boolean;
begin
  v_is_distance_range_valid := p_redemption_billing.is_distance_range_valid(:new.id,
                                                                            :new.redemption_billing_company,
                                                                            :new.distance_min,
                                                                            :new.distance_max,
                                                                            'redemption_billing_mpr');
  if not v_is_distance_range_valid then
    raise_application_error(-20001, 'This is a custom error');
  end if;
end ab_redemption_billing_mpr;

功能：

function is_distance_range_valid(p_id           in number,
                                   p_company      in varchar2,
                                   p_distance_min in number,
                                   p_distance_max in number,
                                   p_table_name   in varchar2) return boolean is
    d_record_number number;
  begin
    execute immediate 'select count(*) from ' || p_table_name || ' r 

        where r.redemption_billing_company = :1 
        and (:2 between r.distance_min and r.distance_max or :3 between r.distance_min and r.distance_max)
        and r.id = nvl(:4, id)'
      into d_record_number
      using p_company, p_distance_min, p_distance_max, p_id;
    if (d_record_number > 0) then
      return false;
    else
      return true;
    end if;
  end;

is_distance_range_valid()就像我预期的那样工作。如果它返回 false，则意味着范围检查无效并且不插入或更新。

当我创建一个场景来捕获这个异常时，oracle 给出了

ORA-04091: 表名发生变化，触发器/函数可能看不到它

select count(*)当我单击调试按钮时，它会指向线。

我不知道为什么我会收到这个错误。提前致谢。

标签： oracleplsqldynamic-sqldatabase-trigger

解决方案

只需检查以下方法是否可以解决您的问题

创建一个日志表，其中包含查找 is_distance_range_valid 验证所需的列，并在您的触发器中插入此日志选项卡。主表上的触发器必须是插入前或更新触发器

在这个日志表上创建一个触发器，并在日志表触发器中进行验证并引发错误。日志表上的触发器必须是插入或更新后触发器。

此外，这仅在该行已经存在于主表中并且它不是当前插入或更新的一部分时才有效。如果需要验证当前插入或更新，那么我们需要使用 :new.column_name 进行验证

测试：-

    create table t_trg( n number);

    create table t_trg_log( n number);


    create or replace trigger trg_t
    before insert or update on t_trg
      for each row
    declare
    l_cnt number;
    begin
    insert into t_trg_log values(:new.n);
    end trg_t;

    create or replace trigger trg_t_log
      after insert or update on t_trg_log
      for each row
    declare
    l_cnt number;
    begin
    select count(1) into l_cnt from t_trg
    where n=:new.n;

    if  l_cnt  > 0  then
        raise_application_error(-20001, 'This is a custom error');
      end if;

    end trg_t_log;

我第一次插入它不会抛出错误

  insert into t_trg values(7);

  1 row inserted.

第二次执行时，我收到自定义错误

   insert into t_trg values(7);

  Error report -
  ORA-20001: This is a custom error
  ORA-06512: at "TRG_T_LOG", line 7
  ORA-04088: error during execution of trigger 'TRG_T_LOG'
  ORA-06512: at "TRG_T", line 4
  ORA-04088: error during execution of trigger 'TRG_T'

更新：-使用复合触发器，在第一次完成插入时抛出错误，因为它还考虑了我们在执行 SELECT 时正在更新或插入的当前行

推荐阅读