Re: PL/SQL problem: Unecessary insert in table

From: Matt B. <mcb_at_fightspam.sd.znet.com>
Date: 2000/06/21
Message-ID: <sl2toed9e7f38_at_corp.supernews.com>

"Tom Henricksen" <thenricksen_at_ciscoinc.com> wrote in message news:394f810f.0_at_news...
> This key-commit trigger is adding data after the third line. When the
> delete occurs I can check the database and there is two rows of data. Then
> when it get to the insert in user_lbl the correct data is input. This makes
> no sense, i have two rows of incorrect data for every correct row.
> Thanks,
> Tom
>
>
> begin
> message('Key-commit');
> delete iac.w_clause;
> message('after delete');
> /* unecesary insert here */
> commit;
> if :var_lbl.fld_nam is not null then
> commit;
> go_record(1);
> loop
> select dba_nam,tab_nam,d_type into :global.dba_nam, :global.tab_nam,
> :global.d_type
> from iac.var_lbl
> where var_lbl.fld_nam = :var_lbl.fld_nam;
> insert into iac.w_clause values(:global.dba_nam,:var_lbl.start_val
> ,:var_lbl.end_val,:global.tab_nam,:global.d_type);
> message('insert w_clause '||:global.dba_nam||' '||:var_lbl.start_val||'
> '||:var_lbl.end_val||' '||:global.tab_nam);
> if :global.flag = 1 then
> --message(':global.flag '||:global.flag);
> if :user_lbl.lbl_nam is not null then
> --message(':user_lbl.lbl_nam '||:user_lbl.lbl_nam);
> /* added 06-19-2000 to fix unecessary inserting TH*/
> insert into iac.user_lbl values(:user_lbl.lbl_nam,:var_lbl.fld_nam
>
> ,:var_lbl.start_val,:var_lbl.end_val,:order_lbl.fld_nam,:user_lbl.lbl_num);
> message('insert user_lbl '||:global.dba_nam||' '||:var_lbl.start_val||'
> '||:var_lbl.end_val||' '||:global.tab_nam);
> end if;
> end if;
> if :SYSTEM.LAST_RECORD = 'TRUE' then
> exit;
> end if;
> next_record;
> end loop;
> commit;
> end if;
> :global.flag := 0;
> go_block('order_lbl');
> exception
> when no_data_found then
> message('FIELD IS NOT AVAILABLE');
> end;

I'm guessing here at what you're trying to do...

1. do a delete and commit (OK)
2. go to the first record and loop through the records in the block (OK).
3. if, when on a record, :var_lbl.fld_nam is not null, you want to do an insert into iac.w_clause. Furthermore, if :global.flag = 1, also do an insert into iac.lbl_nam.
4. Continue until you're all done with all records.

Right?

If so...

1. You have your if-statement for "if :var_lbl.fld_nam is not null" outside the loop. It should be inside. Otherwise it will evaluate that only once for the current record you are on before you even do a go_record(1). It will not evaluate that on a record-by-record basis for every record in the loop. It needs to be inside the loop.
2. You are exiting when you are on the last record and the exit command is at the end of the loop and before the "next_record". This means that most of your loop will still fire for the last record of the block (the exit is at the end of the loop, not at the beginning) and you still probably don't want to execute it for a 'NEW' (not "touched" by the user) record, right? You probably want to exit the loop as soon as you land on the last record in the block *and* it's a blank ('NEW') record. So move the exit command up near the top of the loop and also add in your record status as a criteria for exiting.

Try this:

*I removed a "commit" that essentially was doing nothing *I added in (your choice if you wanna use these or not) some evaluations of FORM_SUCCESS, as you might want to halt processing if the form can't successfully navigate off of a record or not (raise form_trigger_failure to halt processing at the offending record and return control to the user)) *I changed your conditional exit from an "if...exit" to a "exit when..." (less code - just my preference).

begin
  delete iac.w_clause;
  commit;
  go_record(1);
  if not FORM_SUCCESS then

     raise FORM_TRIGGER_FAILURE;
  end if;
  loop
    exit when :SYSTEM.RECORD_STATUS = 'NEW' and

              :SYSTEM.LAST_RECORD = 'TRUE';     if :var_lbl.fld_nam is not null then

      select dba_nam,tab_nam,d_type
      into :global.dba_nam, :global.tab_nam, :global.d_type
      from iac.var_lbl
      where var_lbl.fld_nam = :var_lbl.fld_nam;
      --
      insert into iac.w_clause
      values(:global.dba_nam,:var_lbl.start_val,
             :var_lbl.end_val,:global.tab_nam,
             :global.d_type);
      --
      if :global.flag = 1 then
        if :user_lbl.lbl_nam is not null then
          insert into iac.user_lbl
          values(:user_lbl.lbl_nam,:var_lbl.fld_nam,
                 :var_lbl.start_val,:var_lbl.end_val,
                 :order_lbl.fld_nam,:user_lbl.lbl_num);
        end if;
      end if;
      --

    end if;
    next_record;
    if not FORM_SUCCESS then

       raise FORM_TRIGGER_FAILURE;
    end if;
  end loop;
  commit;
  :global.flag := 0;
  go_block('order_lbl');
exception
  when no_data_found then
    message('FIELD IS NOT AVAILABLE');
end; Received on Wed Jun 21 2000 - 00:00:00 CEST