This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Feb 18, 2013 12:15 PM by 276532 RSS

How do I stop lower level triggers from firing?

276532 Newbie
Currently Being Moderated
I have PRE-INSERT triggers defined at both the form and data block levels. I have the properties for the triggers set so that the form level trigger fires first. Based on certain conditions, I want the insert to not occurr and for the block level trigger to not be executed. (Basically, I want to clear the form and roll back any database changes.) Is there a command or set of commands I can use to stop this from happening? I have tried used the TIMER trick, but the WHEN-TIMER-EXPIRES trigger fires after the block level PRE-INSERT trigger.

Thanks,
John
  • 1. Re: How do I stop lower level triggers from firing?
    HamidHelal Guru
    Currently Being Moderated
    jdlivingston wrote:
    I have a PRE-INSERT triggers defined at both the form and data block levels. I have the properties for the triggers set so that the form level trigger fires first. Based on certain conditions, I want the insert to not occurr and for the block level trigger to not be executed. (Basically, I want to clear the form and roll back any database changes.) Is there a command or set of commands I can use to stop this from happening? I have tried used the TIMER trick, but the WHEN-TIMER-EXPIRES trigger fires after the block level PRE-INSERT trigger.
    hello John

    Check Execution Hierarchy Properties of Pre-Insert Trigger


    Hope this helps
  • 2. Re: How do I stop lower level triggers from firing?
    276532 Newbie
    Currently Being Moderated
    The block level trigger's Execution Hierarchy value was (and is) set to 'AFTER'. I have tried setting the form level trigger's Execution Hierarch value at both 'BEFORE' and 'OVERRIDE' and the block level trigger still fires.
  • 3. Re: How do I stop lower level triggers from firing?
    user346369 Expert
    Currently Being Moderated
    Form-level Execution Hierarchy has NO effect. The form-level trigger runs UNLESS the lower-level trigger's Execution Hierarchy is set to "override".

    You can set a package variable defined in a package specification in your form, and the triggers can check its value any time. But I would use a timer, and check for its existence:

    <pre>If not id_null(find_timer('NO_INSERT')) then... --skip the insert</pre>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  • 4. Re: How do I stop lower level triggers from firing?
    HamidHelal Guru
    Currently Being Moderated
    jdlivingston wrote:
    The block level trigger's Execution Hierarchy value was (and is) set to 'AFTER'. I have tried setting the form level trigger's Execution Hierarch value at both 'BEFORE' and 'OVERRIDE' and the block level trigger still fires.
    Set Execution Hierarchy of Block level (pre-insert) trigger to BEFORE
    Now block level trigger will fire before form level trigger

    Does it works ?
  • 5. Re: How do I stop lower level triggers from firing?
    276532 Newbie
    Currently Being Moderated
    I don't think I was clear enough in my original post. I do NOT want the block level trigger to fire under certain circumstances. I want the form level trigger to fire FIRST, which it does. What I can't get to happen is the prevention of the block level trigger's firing.
  • 6. Re: How do I stop lower level triggers from firing?
    HamidHelal Guru
    Currently Being Moderated
    jdlivingston wrote:
    I don't think I was clear enough in my original post. I do NOT want the block level trigger to fire under certain circumstances. I want the form level trigger to fire FIRST, which it does. What I can't get to happen is the prevention of the block level trigger's firing.
    Hi jdlivingston,
    If you not want to fire block level trigger then add condition and handle the situation. Like
    ................
    IF condition is true then
    do this code...
    else
    null;
    end if;
    Hope this helps
    Hamid
  • 7. Re: How do I stop lower level triggers from firing?
    276532 Newbie
    Currently Being Moderated
    I was hoping that I wouldn't have to alter the block level trigger code. If there isn't a way around it, then I'll do it, but I was hoping there is a command or error I can raise to stop processing before the block level trigger fires.
  • 8. Re: How do I stop lower level triggers from firing?
    HamidHelal Guru
    Currently Being Moderated
    jdlivingston wrote:
    I was hoping that I wouldn't have to alter the block level trigger code. If there isn't a way around it, then I'll do it, but I was hoping there is a command or error I can raise to stop processing before the block level trigger fires.
    Hi jdlivingston,
    Here is a clarification.
    If you want not to fire your form level trigger for some reason then write your code, for example
    If condition is not ok then
    clear_form(no_validate);
    parameter.flag:=1; -- here i create a form parameter and assign a vlaue
    raise form_trigger_failure;
    else
    do the work...
    parameter.flag:=0; -- here i create a form parameter and assign a vlaue
    end if;
    Now at block level trigger
    if parameter.flag = 0 then
    do this
    end if;
    Hope this helps

    Hamid
    If someone's response is helpful or correct, please mark it accordingly.*
  • 9. Re: How do I stop lower level triggers from firing?
    276532 Newbie
    Currently Being Moderated
    What you suggested is what I am trying to avoid. I want to do the following at the form level trigger:

    IF(condition not ok)
    THEN
    Stop Right Here. Do NOT execute any more code other than clearing the form.
    ELSE
    NULL;
    END IF;

    If at all possible, I do not want to have to set a global (or other) parameter which has to be checked at the block (or even item) level
    trigger(s).
  • 10. Re: How do I stop lower level triggers from firing?
    Andreas Weiden Guru
    Currently Being Moderated
    Maybe i miss something, but what abouit a RAISE FORM_TRIGGER_FAILURE; ? It shlould stop the whole transaction process.
  • 11. Re: How do I stop lower level triggers from firing?
    276532 Newbie
    Currently Being Moderated
    I gave that a try earlier and the command seems to be ignored.

    I tried
    IF(bad condition)
    THEN
    RAISE Form_Trigger_Failure;
    END IF;

    The form skips right along to the block level trigger. I tried raising the form_trigger_failure both inside and outside of a DECLARE-BEGIN-END block in the Form level trigger.
  • 12. Re: How do I stop lower level triggers from firing?
    user346369 Expert
    Currently Being Moderated
    jdlivingston wrote:
    I have PRE-INSERT triggers defined at both the form and data block levels. I have the properties for the triggers set so that the form level trigger fires first. Based on certain conditions, I want the insert to not occurr and for the block level trigger to not be executed. (Basically, I want to clear the form and roll back any database changes.) Is there a command or set of commands I can use to stop this from happening? I have tried used the TIMER trick, but the WHEN-TIMER-EXPIRES trigger fires after the block level PRE-INSERT trigger.
    Ok, time to back up. If you want to prevent an insert, you should NOT do it in a pre-insert trigger. Pre-insert is there for you to make last-minute changes to your data, like setting insert_date, or updating other tables, etc.

    If you want to STOP the insert, validation triggers are the place to do that. If you Raise Form_Trigger_Failure in a validation trigger, Oracle Forms will stop processing, and the process will never get to the pre-insert trigger. Why can't you use the When-Validate triggers?

    I have never tried what you are doing in pre-insert (PI triggers are seldom used), but it still seems like a Raise Form_Trigger_Failure issued in either one SHOULD stop processing. However, understand that triggers run and do not automatically check Form_Success before they run. So it makes sense to me that if you Raise FTF in one, the other will still run. Although I would think the entire commit process that started the ball rolling WOULD stop (but maybe not at the point you would expect).
    I was hoping that I wouldn't have to alter the block level trigger code. If there isn't a way around it, then I'll do it, but I was hoping there is a command or error I can raise to stop processing before the block level trigger fires.
    Nope, you need to check the condition in both triggers. Triggers don't signal each other automatically.
    What I can't get to happen is the prevention of the block level trigger's firing.
    That's because you're assuming (I think) that a Raise FTF should stop the second one. But I am pretty sure Raise FTF will not stop the second pre-insert. (I seem to recall that this may be the case with When-Validate-Item triggers, too.) You need this in BOTH triggers:

    <pre> IF(condition not ok) THEN
    Raise Form_Trigger_Failure;</pre>

    Because of that "feature", I seldom will code same-name triggers at two levels. It's easier to put All the processing at a higher level like this:
    <pre> If :System.Trigger_Block = 'BLOCK_A' then
    -- Do Block_A processing --
    Elsif :System.Trigger_Block = 'BLOCK_B' then
    -- Do Block_B processing --
    End if;
    -- Do common processing --</pre>

    Now, if you want to follow your Stop-The-Insert-Process due to some condition with a Clear_Form, etc, then you will probably need to kick off the Clear_Form from a start_timer...When-Timer-Expired setup, since When-Validate and Pre-Iinsert triggers cannot do what you want.
  • 13. Re: How do I stop lower level triggers from firing?
    user346369 Expert
    Currently Being Moderated
    Thinking about this a little more....
    Why can't you put the logic that stops the insert in your final Commit processing? If there are conditions that are checked at the last minute, I put that logic in the processing that is run only at commit time.

    You should be able to do this:

    <pre> Validate(Form_Scope);
    If not Form_Success then
    Raise Form_Trigger_Failure;
    End if;
    If (bad condition) then
    Message('Cannot commit');
    Clear_Form;
    Raise Form_Trigger_Failure;
    End if;
    -- continue here if all is ok --
    Commit_Form;</pre>
  • 14. Re: How do I stop lower level triggers from firing?
    276532 Newbie
    Currently Being Moderated
    Steve, Thank you for your very detailed response.


    >
    Ok, time to back up. If you want to prevent an insert, you should NOT do it in a pre-insert trigger. Pre-insert is there for you to make last-minute changes to your data, like setting insert_date, or updating other tables, etc. >

    Thanks for that information.

    >
    If you want to STOP the insert, validation triggers are the place to do that. If you Raise Form_Trigger_Failure in a validation trigger, Oracle Forms will stop processing, and the process will never get to the pre-insert trigger. Why can't you use the When-Validate triggers? >

    I am going to give this a shot and reply to your second post if it worked.

    >
    That's because you're assuming (I think) that a Raise FTF should stop the second one.
    >

    Yes, that is what I want to happen.

    >
    But I am pretty sure Raise FTF will not stop the second pre-insert.
    >

    You are correct. Based on testing, the block level pre-insert fires.


    >
    Because of that "feature", I seldom will code same-name triggers at two levels. It's easier to put All the processing at a higher level like this:
    If :System.Trigger_Block = 'BLOCK_A' then
               -- Do Block_A processing --
             Elsif :System.Trigger_Block = 'BLOCK_B' then
               -- Do Block_B processing --
             End if;
             -- Do common processing 
    Very cool. I "inherited" the code I am currently supporting and will change the existing code to this trigger model as much as possible.
1 2 Previous Next

Legend

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