This discussion is archived
4 Replies Latest reply: Nov 25, 2012 12:43 AM by user346369 RSS

PRE INSERT TRIGGER not firing

NZK Newbie
Currently Being Moderated
Dear All
I'm using Oracle Forms 6i with 9i Database
I have written the following code.

This procedure will be fired when i press the Save button

PROCEDURE P_COMMIT IS
v_msglvl NUMBER;
BEGIN
IF NOT FORM_SUCCESS THEN
RAISE FORM_TRIGGER_FAILURE;
ELSE
v_msglvl := :SYSTEM.MESSAGE_LEVEL;
:SYSTEM.MESSAGE_LEVEL := 25;
COMMIT_FORM;
:SYSTEM.MESSAGE_LEVEL := v_msglvl;
MESSAGE('1 Record applied and saved');
END IF;
END;

THE PRE-INSERT-TRIGGER WRITTEN AS FOLLOWS

IF FORM_SUCCESS THEN
DECLARE
v_ponum ng_purchase_hd%phd_po#%TYPE;
BEGIN
SELECT ng_po#_seq.NEXTVAL
INTO v_ponum
FROM dual

:ng_purchase_hd.phd_po# := v_ponum;
END;
END IF;

Here it should fire the Pre Insert Trigger when press the save button and it should create unique purchase order number. But pre insert trigger not firing. Please help.

NZK
  • 1. Re: PRE INSERT TRIGGER not firing
    Andreas Weiden Guru
    Currently Being Moderated
    First, i would remove the IF FORM_SUCCESS-part in your PRE-INSERT-trigger.

    Are you sure the trigger doesn't fire at all? Where do have defined the PRE-INSERT-trigger (form, block, item)? Are you sure you have a newly created record in your form? I would also try the same without your KEY-COMMIT-trigger (just to check).
  • 2. Re: PRE INSERT TRIGGER not firing
    NZK Newbie
    Currently Being Moderated
    Dear Andreas

    Thank you for you response.

    As you told me I removed the IF FORM_SUCCESS part from the PRE-INSERT-TRIGGER, except this I didn't change anything. My trigger is created FORM-LEVEL. I made a small change in the :PHD_PO# TEXT ITEM. I changed the REQUIRED to be as NO. Now the trigger is firing and creating unique numbers as well. What could be the reason for this? Thank you for you response
  • 3. Re: PRE INSERT TRIGGER not firing
    Andreas Weiden Guru
    Currently Being Moderated
    Before you can commit, forms makes sure all data in your form is valid. And if you define a visible item as required, forms will check if it has a value, otherwise it cannot commit.
  • 4. Re: PRE INSERT TRIGGER not firing
    user346369 Expert
    Currently Being Moderated
    The problem is here:
      v_msglvl := :SYSTEM.MESSAGE_LEVEL;
      :SYSTEM.MESSAGE_LEVEL := 25;
      COMMIT_FORM;
      :SYSTEM.MESSAGE_LEVEL := v_msglvl;
    By setting Message_Level to 25, you are hiding error messages that you SHOULD see. Setting it to 25 caused your "Field is required" message to be hidden.

    Set Message_Level to zero and never set it higher. If there is any message you need to treat specially, do so in the on-error form-level trigger.

Legend

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