Unable to fire RAISE FORM_TRIGGER_FAILURE [message #678341] |
Mon, 25 November 2019 14:27 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
I have the following code in PL/SQL library under a procedure called MOD$WHEN_VALIDATE_ITEM trigger:
IF GET_SYS_PARAM(NAME_IN('SCTN.BUSINESS_UNIT_ID'), 'CLASS_ID_VARCHAR2_IND') IN('N','Y',NULL)THEN
IF NAME_IN('SCTN.CLASS_ID') = '0000' THEN
MESSAGE('SANDEEP 4 ZEROS'); PAUSE;
rams$msg_alert('2593', 'E', TRUE);
RAISE FORM_TRIGGER_FAILURE;
ELSIF NAME_IN('SCTN.CLASS_ID') = '000' THEN
MESSAGE('SANDEEP 3 ZEROS'); PAUSE;
rams$msg_alert('2593', 'E', TRUE);
raise form_trigger_failure;
ELSIF NAME_IN('SCTN.CLASS_ID') = '00' THEN
MESSAGE('SANDEEP 2 ZEROS'); PAUSE;
rams$msg_alert('2593', 'E', TRUE);
raise form_trigger_failure;
ELSIF NAME_IN('SCTN.CLASS_ID') = '0' THEN
MESSAGE('SANDEEP single ZERO'); PAUSE;
rams$msg_alert('2593', 'E', TRUE);
raise form_trigger_failure;
END IF;
END IF;
This code fires when I enter a class id (in the form) as '0000' or '000' or '00' or '0'. Basically we need to prevent the user from entering all 0's and then ensure that the user is not able to navigate the class id text box.
Everything works fine excepting RAISE FORM_TRIGGER_FAILURE i.e. the alert is fired and then the cursor automatically navigates to the next text item.
I have tried a lot of options including GO_ITEM, but realised that this does not work in PL/SQL libraries as it is outside of the form. I am also new to Oracle forms.
My question is: is there any way to ensure that the cursor stays back in class id text box so that the user corrects this and only then navigates.Thanks in advance.
|
|
|
|
Re: Unable to fire RAISE FORM_TRIGGER_FAILURE [message #678347 is a reply to message #678346] |
Tue, 26 November 2019 06:37 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
From what I recall, I have done that too i.e., put the logic in WHEN-VALIDATE-ITEM trigger under item level and it does not even fire because the trigger's execution is set to 'AFTER'. In my opinion even if it's set to AFTER it should fire, but it just does not seem to.
I will try this again today and let you know.
|
|
|
|
Re: Unable to fire RAISE FORM_TRIGGER_FAILURE [message #678350 is a reply to message #678349] |
Tue, 26 November 2019 20:38 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
I tried the whole thing again and it worked now. Here's what I had to do in order to get it to work:
a) remove the code written in the PL/SQL library and place the code at item level and then unit test it.
it works to my satisfaction but the bigger problem is, convincing my team lead that it's ok to move it from PL/SQL library to item level validation as the code written under FORM LEVEL (which subsequently calls the PL/SQL library) does not 'raise form_trigger_failure'.
There was no other way around this for me.
|
|
|
Re: Unable to fire RAISE FORM_TRIGGER_FAILURE [message #678351 is a reply to message #678349] |
Tue, 26 November 2019 20:47 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
cookiemonster wrote on Tue, 26 November 2019 09:40That implies you have when-validate-item triggers at block or form level.
If so why?
What's in them?
Yes, you are correct. There is a when-validate-item trigger at the form level. What it does is, it calls a procedure and the procedure is in PL/SQL library. Here is where I was encountering the issue. For whatever reason, the PL/SQL library was not 'RAISING' the error or trapping it. Hence I moved the code from PL/SQL library to item level of 'when-validate-item'.
|
|
|
Re: Unable to fire RAISE FORM_TRIGGER_FAILURE [message #678355 is a reply to message #678351] |
Wed, 27 November 2019 03:19 |
cookiemonster
Messages: 13962 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That's not really answering the question.
I've never needed a form level WVI trigger and can't think of a good use for one.
Odds are whatever code you have in that trigger would be better in a different trigger, but without knowing what it's actually checking it's impossible to say.
|
|
|