CODEWITHSUNDEEP
oracle-databasetriggers
Sushil
Sushil

Reputation: 374

Oracle trigger to update all the rows of table based on the data of updating column

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 (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 :)

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

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 means that value is present in incoming data for my requirement.

Upvotes: 0

Views: 1251

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

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

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;

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;

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;

And for all data:

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;

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.

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

This is no answer. I only want to illustrate what you are doing.

Say you have this table:

input_disp_req  region  mode_name  pos_clmn  some_data
Y               R1      M1         1         A
Y               R1      M1         2         B
Y               R1      M1         3         C
N               R1      M1         4         D
N               R1      M1         5         E

Now let's take this update statement:

update fce_template set input_disp_req = 'Y' where input_disp_req = 'N';

This would update two records, so your trigger gets fired twice. On first execution there will be four records to give the sequence numbers 6 to 9 to. On second execution all five records will be 'Y' and get the sequence numbers 10 to 14. So after your update your records may look like this:

input_disp_req  region  mode_name  pos_clmn  some_data
Y               R1      M1         14        A
Y               R1      M1         10        B
Y               R1      M1         12        C
Y               R1      M1         13        D
Y               R1      M1         11        E

What do you gain by this? It doesn't seem to make sense. It seems that you are using a wrong approch to something you want to achieve.

Upvotes: 1

przemo_pl
przemo_pl

Reputation: 87

you can try to use the compound trigger in a manner: after each row: get the rowid or the key column of the updated row and put it into a pl/sql collection

after statement: run through all the rows and update the sequence

it should get rid of the "mutating table" error...

something like that (it may be missing some var declaration):

    create or replace trigger your_trigger
   for update on your_table
   compound trigger

   --Trigger level variables so we capture all updates.
   type t_rec is record(
      flg varchar2(1));
   type t_list_tab is table of t_rec index by pls_integer;
   l_tab t_list_tab;

after each row is

            l_new_the_id := :new.the_id;
            l_old_the_id := :old.the_id;

            if (not l_tab.exists(l_new_the_id)) then
               l_cnt_tab(l_new_the_id).flg := '';
            end if;


end after each row;

after statement is

      if (l_tab.count != 0) then

         for l_idx in l_tab.first .. l_tab.last loop
               --your updates here
          end loop;
      end if;

end after statement;

end;

Probably you just NEED TO use triggers but everyone will just tell you that you shouldn't - triggers are pain in the ass when you try to track the changes or find a bug - probably better would be to use a procedure to update the table and do all the stuff using its logic...

Upvotes: 2

Simimmo
Simimmo

Reputation: 668

You can try making the trigger before update:

CREATE OR REPLACE TRIGGER fce_trigger
   BEFORE UPDATE of input_disp_req ON fce_template

Upvotes: 0

Related Questions