CODEWITHSUNDEEP
sqloracle-databasetriggers
ivanz
ivanz

Reputation: 825

Oracle invalid trigger ORA-04098

I cannot figure out where is the problem. here is my trigger my produkt table has attributes: produktid, name, manufacturing_price, sale_price i am trying to make sale price 1.1 value of manu price

CREATE OR REPLACE TRIGGER  "predajna_cena" 
AFTER update of "MANUFACTURING_PRICE" on "PRODUKT"
begin
UPDATE PRODUKT SET SALE_PRICE = MANUFACTURING_PRICE*1.1;
end;
/
ALTER TRIGGER  "predajna_cena" ENABLE;

apex is showing Object Status INVALID

and i cannot change manufacturing price error ORA-04098: trigger ' ' is invalid and failed re-validation

thanks

Upvotes: 0

Views: 613

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 52040

CREATE OR REPLACE TRIGGER  "predajna_cena" 
AFTER update of "MANUFACTURING_PRICE" on "PRODUKT"
begin
UPDATE PRODUKT SET SALE_PRICE = MANUFACTURING_PRICE*1.1;
end;
/

If you were able to make this trigger to work, it will update all prices after each and every update of the table "PRODUKT". Probably not what you want.

For such case, you should use a BEFORE UPDATE ... FOR EACH ROW trigger, changing the product price before inserting it :NEW."SALE_PRICE" = :NEW."MANUFACTURING_PRICE"*1.1.

Something like that maybe:

CREATE OR REPLACE TRIGGER  "predajna_cena" 
BEFORE UPDATE OF "MANUFACTURING_PRICE" ON "PRODUKT"
FOR EACH ROW
BEGIN
  :NEW.SET SALE_PRICE := :NEW.MANUFACTURING_PRICE*1.1;
END;

Untested. Beware of typos!

Depending your needs an other option would use a virtual column instead of a trigger to achieve the same result.

DROP TRIGGER "predajna_cena";
ALTER TABLE "PRODUKT" DROP COLUMN "MANUFACTURING_PRICE";
ALTER TABLE "PRODUKT" ADD "MANUFACTURING_PRICE" 
                      GENERATED ALWAYS AS ("SALE_PRICE" * 1.1) VIRTUAL;

Untested. Beware of typos!

Upvotes: 2

Related Questions