This discussion is archived
5 Replies Latest reply: Feb 19, 2013 9:18 PM by O.Developer RSS

When-validate-item and after delete record

937105 Newbie
Currently Being Moderated
Hi! First, sorry for my english.

I have a block from a table (PRODUCTOS) with these fields:(PRODUCTO_NO(id),DESCRIPCION,PRECIO_ACTUAL,STOCK_DISPONIBLE)

I need write a code on producto_no field and when executes "when-validate-item" this fill all fields with the correct data.

BEGIN
SELECT producto_no, descripcion, precio_actual, stock_disponible
INTO :productos.producto_no, :productos.descripcion, :productos.precio_actual, :productos.stock_disponible
FROM productos
WHERE producto_no = :PRODUCTOS.PRODUCTO_NO;

EXCEPTION
.......
END;
This works well, but I want to use, insert_record, delete_record, buttons and this dont "surf" for the database data. Any way of go to wanted record to use on when-validate-item?

Another way: On my Delete button (On-press-button) I have this code:

BEGIN
DELETE FROM PRODUCTOS WHERE PRODUCTO_NO = :PRODUCTO_NO;
COMMIT; --2 VECES SI FUNCIONA
EXCEPTION
WHEN OTHERS THEN
message('ESE REGISTRO AÚN NO EXIS EN LA BASE DE DATOS. NO SE PUEDE ELIMINAR.');
END;
This only works if I press the button two times, the first time not works :S

Sorry my english. Thanks a lot for the help!

Edited by: user8865151 on 17-feb-2013 10:58
  • 1. Re: When-validate-item and after delete record
    Amatu Allah Guru
    Currently Being Moderated
    Hi
    pls replace the commit with
    Forms_DDL('COMMIT');
    Amatu Allah
  • 2. Re: When-validate-item and after delete record
    user346369 Expert
    Currently Being Moderated
    First, do NOT use Forms_DDL('Commit')! Using that completely ruins all the automatic processing features that Forms provides!

    If you want to populate a new record with data using your select, When-Validate-Item will work, but it should be at the block level, so entering anything in any field will cause the select to run. But it would be tricky -- you would only want to select data into the fields once.

    It might be easier if you used a When-New-Item-Instance trigger at the block level. If the trigger detected that :System.Record_Status='NEW', it could run the select.

    On your Delete button, all you should do is Delete_Record; Then when you do a commit, Oracle Forms automatic processing will create and run the SQL statement required to remove the record from the database. You really shouldn't put a Commit; in your delete processing. It should only be run from a "Save" or "Submit" button. ....or, you COULD do a Delete_Record; then Commit; That would remove the record and save the changes.

    You should never write the sql to delete or update -- Forms does that for you automatically.
  • 3. Re: When-validate-item and after delete record
    937105 Newbie
    Currently Being Moderated
    The problem, is that I have to get all the values of the select when you write a product number, and I make these how write on first post. Then in my block I haven't record, only text created for my from a select sentence.

    Then my DELETE_RECORD not works. I have to use, EXECUTE_QUERY on new form instance of my block? And I have problem selecting the wanted record.

    Sorry my english, If you have problems with my explain, I can put some images and more details.

    Thanks a lot :)
  • 4. Re: When-validate-item and after delete record
    Andreas Weiden Guru
    Currently Being Moderated
    I guess your block is a database block? If so, this is what i guess what happens:

    You type in the productnumber in a textfield, then the WHEN-VALIDATE-ITEM-trigger reads all the other values. As you type the values in a new record in a database block, the record is marked as "to be inserted".
    Then you issue the DELETE-statement in your button. This will delete the existing record in the database.
    Then the COMMIT starts. This commit applies all changes from the form to the DB, and this will issue an INSERT with the data in your block, as it was marked "to be inserted" earlier.
    Then the DB-commit is executed and you have deleted a record and at the same time inserted a new one.
    Now you your button gets called a second time.
    The delete happens as in the first case.
    The record in forms was already inserted during the last COMMIT-processing, so this doesn't happen anymore
    Then the DB-commit is executed and you have deleted a record.

    If you want to use your block for "data selection", you have to change it to ENTER_QUERY-mode first.
  • 5. Re: When-validate-item and after delete record
    O.Developer Journeyer
    Currently Being Moderated
    Hi,


    You have to do two things properly.

    First Define what exactly you need.

    Secondly, you should understand the basic oracle from concept/structure. If you under stood, forms flow you requirement will be meet easily.

    ------------------

    To your problem,

    use Trigger when validate item
    And you can use ON_delete/On Insert.
    You cannot use go to particular record withing 'when validate item( restricted trigger), instead try to use PUSHBUtto which will enable you using go_record.

    --------------------------------

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points