Home » Developer & Programmer » Forms » pre-insert trigger
pre-insert trigger [message #677595] Sun, 29 September 2019 14:20 Go to next message
tayshaun
Messages: 4
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 21514
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 Go to previous messageGo to next message
cookiemonster
Messages: 13747
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?
Re: pre-insert trigger [message #677632 is a reply to message #677618] Tue, 01 October 2019 10:11 Go to previous messageGo to next message
tayshaun
Messages: 4
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
Re: pre-insert trigger [message #677642 is a reply to message #677632] Wed, 02 October 2019 05:47 Go to previous message
cookiemonster
Messages: 13747
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?
Previous Topic: Sending Email through Oracle Forms 6i
Next Topic: image in database and retrieve
Goto Forum:
  


Current Time: Sun Dec 08 09:56:31 CST 2019