Oracle invalid trigger ORA-04098

Question

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

If that factor (relation) is always valid you could use a virtual column instead. — user2672165
Thou probably nothing to do with your problem: Do you explicitly want your trigger-name in lower case (which is achieved by the double quotes)? Oracle standard would be (pseudo) case-insensitive by converting everything to upper implicit. You should only use case-sensitive naming if you have to/want to. — evilive

Sylvain Leroux Sylvain Leroux · Accepted Answer · 2014-12-19T09:17:00

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!}

Oracle invalid trigger ORA-04098

1 Answers