Problem in the condition of Trigger - PL-SQL

Asked

Viewed 118 times

0

My teacher passed that question:

Write a Rigger that checks, during registration/change of a sale, the following:

1) The QUANTITY field of the ITEMVENDA table shall not have decimal places and shall be greater than or equal to 1;

2) The VALUE field of the ITEMVENDA table must be greater than 0.

When the above conditions are not met, the transaction must be cancelled and one of the following messages error should be displayed respectively:

1) "The quantity of the product shall not be fractionated and shall be greater than or equal to 1";

2) "The value of the product shall be greater than 0".

So I made the code:

    CREATE OR replace TRIGGER especificacoes
  BEFORE INSERT OR UPDATE ON itemvenda 
  FOR EACH ROW 
  WHEN (new.quantidade <> round(new.quantidade) OR (new.quantidade < 1) OR (new.valor < 1)) 
BEGIN 
 IF (:new.quantidade <> round(:new.quantidade)) OR (:new.quantidade < 1)
 THEN
 RAISE_APPLICATION_ERROR(-20010,'“A quantidade do produto não pode ser fracionada e deve ser maior ou igual a 1”; ');
 END IF;

 IF (:new.valor < 1 )
 THEN
 RAISE_APPLICATION_ERROR(-20022,'“O valor do produto deve ser maior que 0”.');
 END IF;
END;

SQL Developer compiled normally and ran some tests (UPDATE AND INSERT), the conditions related to the quantity column are not working. Theoretically, it should appear: "The quantity of the product cannot be fractionated and must be greater than or equal to 1" and block the operation.

This is the error that appears when you have a wrong operation in the value column:

"UPDATE "SYSTEM"." ITEMVENDA" SET VALUE = '0' WHERE ROWID = 'Aaasnqaabaaaagxaae' AND ORA_ROWSCN = '2140564' ORA-20022: "The value of the product must be greater than 0". ORA-06512: in "SYSTEM.SPECIFICATIONS", line 9 ORA-04088: error while executing 'SYSTEM.SPECIFICATIONS''

An error occurred while saving changes to the "SYSTEM"." ITEMVENDA" table: Line 5: ORA-20022: "Product value must be greater than 0". ORA-06512: in "SYSTEM.SPECIFICATIONS", line 9 ORA-04088: error while executing 'SYSTEM.SPECIFICATIONS''"

I am unable to find the error, will this round is breaking the condition of the column quantity?.

pl-sql sqldeveloper

  • I did not understand , was made an update or Insert for the quantity ?

  • I did and he accepted normally.

  • Theoretically, it should appear: "The quantity of the product cannot be fractionated and must be greater than or equal to 1" and block the operation.

  • Edits the post and publishes.

1 answer

0


Teacher’s answer:

The only problem that exists in its code is in the use of double quotes in RAISE_APPLICATION_ERROR. Instead of being like this: RAISE_APPLICATION_ERROR(-20010,'"The quantity of the product shall not be fractionated and shall be greater than or equal to 1"; ');

It has to be like this: RAISE_APPLICATION_ERROR(-20010,'The quantity of the product shall not be fractionated and shall be greater than or equal to 1.');

Regarding your logic, there is no mistake. When trying to place the QUANTITY field of the ITEMVENDA table with fractionated value (e.g.: 4.7) Oracle rounds it automatically, because in the table it was declared as NUMBER(5) - without decimal part.

If the QUANTITY field in the ITEMVENDA table is changed to NUMBER(6,1), it will start displaying the message for fractionated values you have programmed.

Browser other questions tagged pl-sql sqldeveloper

You are not signed in. Login or sign up in order to post.