CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
kinderproc
kinderproc

Reputation: 308

compound trigger variable initialization

I have a trigger that looks like this:

create or replace trigger xxlm_rms_mdm123_aid
  for insert or delete on uda_item_lov
  compound trigger

  type xxlm_rms_mdm123_aid_rec is record(
    item      item_master.item%TYPE,
    uda_value uda_item_lov.uda_value%TYPE);

  type xxlm_rms_mdm123_aid_tbl is table of xxlm_rms_mdm123_aid_rec index by pls_integer;

  L_ctgpro_flag      number;
  L_data_tbl         xxlm_rms_mdm123_aid_tbl;
  L_data_tbl_idx     number;
  L_item             item_master.item%TYPE;
  L_wldpro_uda_id    code_detail.code_desc%TYPE;
  L_ctgpro_uda_id    code_detail.code_desc%TYPE;
  L_wldpro_uda_value uda_item_lov.uda_value%TYPE;

  before statement is
  begin
    L_data_tbl.delete;
    L_data_tbl_idx := 0;
  end before statement;

  before each row is
  begin
    select sign(count(1))
      into L_ctgpro_flag
      from code_detail cd
     where cd.code_type = 'X123'
       and cd.code = 'CTGPRO'
       and cd.code_desc = coalesce(:new.uda_id, :old.uda_id);

    if L_ctgpro_flag = 1 then
      L_data_tbl_idx := L_data_tbl_idx + 1;
      L_data_tbl(L_data_tbl_idx).item := coalesce(:old.item, :new.item);
      L_data_tbl(L_data_tbl_idx).uda_value := coalesce(:old.uda_value,
                                                       :new.uda_value);
    end if;
  end before each row;

  after statement is
  begin
    if L_data_tbl.count > 0 then         
      select max(code_desc)
        into L_wldpro_uda_id
        from code_detail
       where code_type = 'X123'
         and code = 'WLDPRO';

      select max(code_desc)
        into L_ctgpro_uda_id
        from code_detail
       where code_type = 'X123'
         and code = 'CTGPRO';

      if L_wldpro_uda_id is not null then
        for idx in 1 .. L_data_tbl.count loop
          L_item := L_data_tbl(idx).item;

          select max(substr(uv.uda_value_desc, 1, 2))
            into L_wldpro_uda_value
            from uda_values uv
           where uv.uda_id = L_ctgpro_uda_id
             and uv.uda_value = L_data_tbl(idx).uda_value;

          if L_wldpro_uda_value is not null then
            if inserting then
              insert into uda_item_lov
                (item,
                 uda_id,
                 uda_value,
                 create_datetime,
                 last_update_datetime,
                 last_update_id)
              values
                (L_item,
                 L_wldpro_uda_id,
                 L_wldpro_uda_value,
                 sysdate,
                 sysdate,
                 user);
            elsif deleting then
              delete uda_item_lov
               where item = L_item
                 and uda_id = L_wldpro_uda_id
                 and uda_value = L_wldpro_uda_value;
            end if;
          end if;
        end loop;
      end if;
    end if;
  end after statement;
end;

It gives a numeric error, that saying that L_data_tbl_idx is null. And for me it looks strange, because it should be initialized with before statement. If I initialize variable, during declaration, like this

L_data_tbl         xxlm_rms_mdm123_aid_tbl;
L_data_tbl_idx     number := 0;  -- <-- here it is
L_item             item_master.item%TYPE;

It works fine. Can someone tell me what happens here and why variable isn't initialized at the first case? Thanks!

Upvotes: 0

Views: 1135

Answers (3)

kinderproc
kinderproc

Reputation: 308

I want to say that before statement works as intended. At the end problem was found and it was not related to the trigger, but to the code that inserts the records.

Upvotes: 0

Koen Lostrie
Koen Lostrie

Reputation: 18675

Check the documentation on compound trigger:

"The optional declarative part of a compound trigger declares variables and subprograms that all of its timing-point sections can use. When the trigger fires, the declarative part runs before any timing-point sections run. The variables and subprograms exist for the duration of the triggering statement."

So in your case it errors out because your are initialising l_data_tbl_idx in a timing-point section and not in the declarative part. The initialisation in the before statement section only exists in the scope of that timing-point section.

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

I would suggest this way:

CREATE OR REPLACE TRIGGER xxlm_rms_mdm123_aid
  FOR INSERT OR DELETE ON uda_item_lov
  COMPOUND TRIGGER

  TYPE xxlm_rms_mdm123_aid_rec IS record(
    item      item_master.item%TYPE,
    uda_value uda_item_lov.uda_value%TYPE);

  TYPE xxlm_rms_mdm123_aid_tbl IS TABLE OF xxlm_rms_mdm123_aid_rec; -- without INDEX BY PLS_INTEGER

  L_data_tbl         xxlm_rms_mdm123_aid_tbl;

  BEFORE STATEMENT IS
  BEGIN
    L_data_tbl := xxlm_rms_mdm123_aid_tbl();
  END BEFORE STATEMENT;


  BEFORE EACH ROW IS
  BEGIN
      ...
    L_data_tbl.extend;
    L_data_tbl(L_data_tbl.last).item := COALESCE(:old.item, :new.item);
    L_data_tbl(L_data_tbl.last).uda_value := COALESCE(:old.uda_value, :new.uda_value);

  END BEFORE EACH ROW;

Then you don't have to maintain the L_data_tbl_idx index.

Upvotes: 0

Related Questions