Oracle触发器根据更新列的数据更新表的所有行 [英] Oracle trigger to update all the rows of table based on the data of updating column

问题描述

我的要求是更新表中input_disp_req ='Y'的所有行的序列号，并且在更新列 input_disp_req 时请帮助我创建触发器.即使在更新后使用过，我也会收到错误消息.

My requirement is to update sequence no of all the rows in table where input_disp_req ='Y' and when updating column input_disp_req Please help me in creating the trigger. I am getting error even though i have used after update.

ORA-04091(表fce_template正在变异.触发器/函数可能看不到它)

ORA-04091 (table fce_template is mutating. Trigger/function might not see it)

CREATE OR REPLACE TRIGGER fce_trigger
   AFTER UPDATE of input_disp_req ON fce_template
  for each row
BEGIN
      update fce_template
         set pos_clmn = fce_seq.nextval
       where region = :new.region
         and mode_name = :new.mode_name
         and input_disp_req = 'Y';
END;

请告知我是否还有其他方法可以执行此操作.请帮我.提前非常感谢:)

Please let me know if there is any other way to do this. Please help me. Thanks a lot in advance :)

共有3列input_disp_req，pos_clmn和pos_seq ...

there are 3 columns input_disp_req, pos_clmn and pos_seq...

• 列input_disp_req包含值为Y或N(类型为 有效/无效指示器)
  • pos_seq列不维护序列，而与input_disp_req列中的Y或N无关
  • pos_clmn列基于input_disp_req = Y的值维护序列号
  • column input_disp_req contains value as Y or N (kind of active/inactive indicator)
    • column pos_seq maintains sequence no irrespective of Y or N in input_disp_req column
    • column pos_clmn maintains sequence no based on value of input_disp_req = Y

    所以我的要求是，每当我将input_disp_req列中的值更新为N/Y触发器时， 为pos_seq排序的所有匹配行更新pos_clmn列的序列值.

    so my requirement here is that when ever I update value in column input_disp_req to N/Y trigger should update the sequence values of column pos_clmn for all the matching rows ordered by pos_seq.

    input_disp_req = Y表示在传入数据中存在符合我要求的值.

    input_disp_req=Y means that value is present in incoming data for my requirement.

    推荐答案

    让我们看看我能否理解您的要求以及您到目前为止所做的评论...

    Let's see if I can make sense of your request and the comments you have made so far...

    似乎已经有一列代表您的订单(您对DavidAldridge的评论).假设它称为order_col.这样就可以按该顺序检索记录:

    It seems there already is a column that represents an order for you (your comment to DavidAldridge). Let's say it is called order_col. This makes it possible to retrieve the records in that order:

    select *
    from fce_template
    order by order_col;
    

    您还可以按顺序获取"Y"记录:

    You can also get the 'Y' records out in order:

    select *
    from fce_template
    where input_disp_req = 'Y'
    order by order_col;
    

    对于某些区域和模式，也是如此:

    And also for a certain region and mode:

    select *
    from fce_template
    where region = :region
    and mode_name = :mode_name
    and input_disp_req = 'Y'
    order by order_col;
    

    但是以某种方式，这对于您来说似乎还不够.您是否只想应用行号(在区域和模式内)?可以使用ROW_NUMBER来完成:

    But somehow this seems not enough for you. Do you simply want to apply row numbers (within region and mode)? That can be done with ROW_NUMBER:

    select f.*, row_number() over (order by order_col) as pos_clmn
    from fce_template f
    where region = :region
    and mode_name = :mode_name
    and input_disp_req = 'Y'
    order by order_col;
    

    对于所有数据:

    select 
      f.*, 
      row_number() over (partition by input_disp_req, region, mode_name
                         order by order_col) as pos_clmn
    from fce_template f
    where input_disp_req = 'Y'
    order by order_col;
    

    如果这是您想要的，您总是可以随时创建行号.您不必存储它们.存储它们甚至会很糟糕，因为您将引入冗余:订单将两次给出，一次由order_col发出，一次由pos_clmn发出.不要那样做.

    If this is what you want, you see, you can always create row numbers on the fly. You don't have to store them. It would even be bad to store them, because you would introduce redundance: The order would be given twice, once by order_col, once by pos_clmn. Don't do that.

    这篇关于Oracle触发器根据更新列的数据更新表的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！