Oracle Forms: alert in pre-delete trigger not working

Question

I have a table OBJECTS with 4 columns: id, type_id, type_desc, and state_id.

If the state_id is 'DELETE', I want to abort the deletion of a row and display an alert message saying "You cannot delete this row". Here's the PRE_DELETE trigger:

begin
  if :OBJECTS.state_id = 'DELETE' then
    set_item_instance_property('OBJECTS.state_id', CURRENT_RECORD, VISUAL_ATTRIBUTE, 'ERROR');

    if show_alert('ALERT_DEL') = alert_button1 then
      null;
    end if;

    raise FORM_TRIGGER_FAILURE;
  end if;
end;

Unfortunately, the alert message won't display.

Edit: I forgot to mention this before: Oracle Forms returns an error FRM-40741: Unable to locate record 0 on block OBJECTS.

Edit: I'm also trying to do a PRE-UPDATE trigger:

declare 
  stateID varchar2(10);
  pressed_button number(2,0);
  info number(2,0);
begin
  stateID := get_item_property(:OBJECTS.state_id, DATABASE_VALUE);

  set_alert_property('ALERT_INFO',alert_message_text,'STATE_ID: ' || stateID);
  info := show_alert('ALERT_INFO');

  if stateID = 'DELETE' then
    set_item_instance_property('OBJECTS.state_id', CURRENT_RECORD, VISUAL_ATTRIBUTE, 'ERROR');
    pressed_button := show_alert('ALERT_EDIT');
    raise FORM_TRIGGER_FAILURE;
  end if;

end;

I get an error FRM-40105:Unable to resolve reference to item DELETE. So I created ALERT_INFO alert to show the value of state_id stored in the database. I shows nothing. What am I doing wrong?

Answer 1

Don't have Forms to test but the use of SHOW_ALERT() in a IF statement looks, um , iffy. Try this instead:

declare
  pressed_button number(2,0);
begin
  if :OBJECTS.state_id = 'DELETE' then
    set_item_instance_property('OBJECTS.state_id', CURRENT_RECORD, VISUAL_ATTRIBUTE, 'ERROR');

   pressed_button := show_alert('ALERT_DEL');

    raise FORM_TRIGGER_FAILURE;
  end if;
end;

You can add a test on pressed_button to see which actual button was pressed, but it doesn't seem to make any difference in the code you posted.

Answer 2

Your code seems to be OK. if I try simplified code in PRE-DELETE trigger

begin
    if show_alert('ALERT_DEL') = alert_button1 then
      raise form_trigger_failure;
    end if;
end;

it works, like expected (Oracle Forms 6i).

I guess your yout :OBJECTS.state_id is not 'DELETE'.

Edit: FRM-40741 - that's it! problem is on line

set_item_instance_property('OBJECTS.state_id', CURRENT_RECORD, VISUAL_ATTRIBUTE, 'ERROR');

You try to set visual property of deleted record, CURRENT_RECORD variable is set to 0 in your case.

Don't use PRE-DELETE trigger for your task. Disable record deletion in WHEN-NEW-RECORD-INSTANCE trigger on OBJECTS block:

if :OBJECTS.state_id = 'DELETE' then
    set_block_property('OBJECTS', DELETE_ALLOWED, PROPERTY_FALSE);
else
    set_block_property('OBJECTS', DELETE_ALLOWED, PROPERTY_TRUE);
end if;