I am trying to create a trigger to automatic generate and insert values with year and month (YYYYMM) into a table as inserts are made into another table.

Example: As inserts are made into table 'original_table'

create table original_table 
(opt_value char(2),
 low_value varchar2(24),
 high_value varchar2(24));
 
 create table new_values
 (id_values varchar2(24),   
  yr_month number(6));

Insert into original_table(opt_value,low_value,high_value) values ('EQ', '1111111111', '1111111111');
Insert into original_table(opt_value,low_value,high_value) values ('BT', '2222222000', '2222222999');
Insert into original_table(opt_value,low_value,high_value) values ('BT', '3333333350', '3333333399');

original_table

Obs: Where EQ stands for 'equal' and BT 'between'. When 'EQ' just need to insert one of the values low or high don't matter, when 'BT' need to generate all the numbers between the two values and insert then into 'new_values' table.

table 'new_values' should get:

new_values

I create a trigger which works, but i find it a little slow, and i don't like to use BEFORE INSERT statement, but can't use AFTER INSERT without getting mutating table error.

create or replace TRIGGER TRG_NAME
    BEFORE INSERT 
    ON original_table
    FOR EACH ROW
BEGIN

    IF :NEW.opt_value = 'BT' THEN
        INSERT INTO new_values (id_values, yr_month) 
        with tab123 (h_value, l_value, y_month)
                  as (select :NEW.high_value, cast(:NEW.low_value as number) , to_char(trunc(sysdate), 'YYYYMM')
                     from original_table 
                     union all
                     select h_value, l_value  1, y_month
                        from tab123
                       where l_value < h_value)
            select distinct  l_value, y_month
              from tab123;

    ELSIF :NEW.opt_value = 'EQ' THEN
         INSERT INTO new_values (id_values, yr_month) values
             ( :NEW.high_value, to_char(add_months(trunc(sysdate), -1), 'YYYYMM'));    
    END IF;
END;

Any tips in how to improve this code will be much appreciated.

CodePudding user response：

You get a mutating table error, when you read from the table that is changing.

It's weird that you are getting it in an AFTER INSERT trigger and not with a BEFORE INSERT trigger. I would have assumed them to both result in the same error, because in both scenarios you read from the triggering table in your trigger. Well, this may have to do with only inserting one row. If you insert more rows at a time, you may get the error with both trigger variants.

In your case reading all the rows from the original_table in your trigger only produces duplicates anyway, that you fend off with DISTINCT. Instead, don't read from the table. It is not necessary. Read from DUAL instead, to get one row with the desired values:

with tab123 (h_value, l_value, y_month) as 
(
  select :new.high_value, cast(:new.low_value as number), to_char(sysdate, 'yyyymm')
  from dual
  union all
  select h_value, l_value   1, y_month
  from tab123
  where l_value < h_value
)
select l_value, y_month
from tab123;

This will get you rid of the mutating table error and also speed up the trigger.