OraFAQ Forum: Forms » pre-insert trigger

Home » Developer & Programmer » Forms » pre-insert trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

pre-insert trigger [message #677595]

Sun, 29 September 2019 14:20

tayshaun
Messages: 5
Registered: September 2019

Junior Member

have a problem in a form, which is that I have a pre-insert trigger for when I exceed the quantity, it shows me a message, but when I exceed it, it does not show me any message and it allows me to save the record, and if the amount is exceeded you should not save and show me the message, which help would be appreciated.

 
DECLARE
   v_cant_e  NUMBER (5);
   v_cant_s  NUMBER (5);
   v_total   NUMBER (5);
BEGIN
   BEGIN
      SELECT SUM (quantity)
        INTO v_cant_e
        FROM mov
       WHERE     cod_mov = :mov.cod_mov
             AND date_mov <= :mov.date_mov
             AND signo = '+';
   EXCEPTION
      WHEN OTHERS
      THEN
         v_cant_e := 0;
   END;

   BEGIN
      SELECT SUM (quantity)
        INTO v_cant_s
        FROM mov
       WHERE     cod_mov = :mov.cod_mov
             AND date_mov <= :mov.date_mov
             AND signo = '-';
   EXCEPTION
      WHEN OTHERS
      THEN
         v_cant_s := 0;
   END;

   v_total := v_cant_e - v_cant_s;

   IF v_total < 0
   THEN
      MESSAGE ('quantity exceeded on Article ' || :mov.cod_mov);
      MESSAGE ('quantity exceeded on Article ' || :mov.cod_mov);

      RAISE form_trigger_failure;
   END IF;
END;

[EDITED by LF: fixed code formatting]

[Updated on: Mon, 30 September 2019 05:40] by Moderator

Report message to a moderator

Re: pre-insert trigger [message #677597 is a reply to message #677595]

Mon, 30 September 2019 03:13

Neveen2019
Messages: 13
Registered: February 2019

Junior Member

Hi
First Of all, you have to initialize your
v_total:=0;
This condition if v_total < 0 then is not true yet to show the message. Try to add an else statement for testing.
I recommend to debug your code or put testing messages for each select statement to check the actual v_cant_e & v_cant_s values.

Amatu Allah.

[Updated on: Mon, 30 September 2019 03:14]

Report message to a moderator

Re: pre-insert trigger [message #677600 is a reply to message #677597]

Mon, 30 September 2019 05:43

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You don't need PL/SQL blocks for those selects; what will raise WHEN_OTHERS? SUM will return NULL if conditions are not met (so - use NVL), but won't return an error.

Simplified & hopefully better code:

DECLARE
   v_cant_e  NUMBER (5);
   v_cant_s  NUMBER (5);
   v_total   NUMBER (5);
BEGIN
   SELECT NVL (SUM (quantity), 0)
     INTO v_cant_e
     FROM mov
    WHERE     cod_mov = :mov.cod_mov
          AND date_mov <= :mov.date_mov
          AND signo = '+';

   SELECT NVL (SUM (quantity), 0)
     INTO v_cant_s
     FROM mov
    WHERE     cod_mov = :mov.cod_mov
          AND date_mov <= :mov.date_mov
          AND signo = '-';

   v_total := v_cant_e - v_cant_s;

   IF v_total < 0
   THEN
      MESSAGE ('quantity exceeded on Article ' || :mov.cod_mov);
      MESSAGE ('quantity exceeded on Article ' || :mov.cod_mov);

      RAISE form_trigger_failure;
   END IF;
END;

[Updated on: Mon, 30 September 2019 05:44]

Report message to a moderator

Re: pre-insert trigger [message #677618 is a reply to message #677597]

Tue, 01 October 2019 03:55

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

Neveen2019 wrote on Mon, 30 September 2019 09:13

Hi
First Of all, you have to initialize your
v_total:=0;

You really don't.
Any value it's intialized to will be overwritten by the assignment immediately before the IF. So initializing it accomplishes nothing.

@tayshaun:

To expand on LF's point - Selects that select aggregate functions (sum, count, min, max ....) without a group by will always return 1 row. If there is no matching data the values of the aggregates will be null (except count which will be 0).
If you were expecting those exception handlers to be trigger you were wrong (and you should be using the specific exception you expect (probably no_data_found) rather than when others.
So if there's no data you'll get
v_total := null - null
which is null, and null is not less than 0.

Also, is the quantity of the record you're trying to insert not relevant to the calculation?

Report message to a moderator

Re: pre-insert trigger [message #677632 is a reply to message #677618]

Tue, 01 October 2019 10:11

tayshaun
Messages: 5
Registered: September 2019

Junior Member

thanks for the replies, the detail is that you should not insert me when the quantity is greater than the existence, how can I solve this since in the database there is a record with a quantity and I am introducing an output with more quantity than I have

Report message to a moderator

Re: pre-insert trigger [message #677642 is a reply to message #677632]

Wed, 02 October 2019 05:47

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well LFs revised code should give you the current value and then you just have to compare it to the quantity in the current datablock record.
Have you tried LFs code?

What problem are you having now?

Report message to a moderator

Previous Topic:	Sending Email through Oracle Forms 6i
Next Topic:	image in database and retrieve

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 22 22:28:30 CST 2025