Call object member function in a DML trigger after update (ORACLE database)

Question

I have a database with a table PRODUCTS of objects Product where the PRIMARY KEY is the EAN field. Every time a row is updated I want to call a member function of the new object Product modified. I made this trigger:

CREATE OR REPLACE TRIGGER SHOW_PROFIT_PERCENTAGE
AFTER UPDATE ON PRODUCTS
FOR EACH ROW

DECLARE

P  Product;

BEGIN

    SELECT VALUE(PS) INTO P
    FROM PRODUCTS PS
    WHERE EAN = :NEW.EAN;

    DBMS_OUTPUT.PUT_LINE('Profit percentage: ' || P.getProfit());

END;

but when I update PRODUCTS it gives me this error:

table PRODUCTS is mutating, trigger/function may not see it

as I suspected. Is there a way to do that? Thanks!

Answer 1

You can't look up the row (object) from the table, as that causes the mutating table error you saw and expected to see.

You can refer to the affected object directly, at least from 10g:

You can use the OBJECT_VALUE pseudocolumn in a trigger on an object table since 10g Release 1 (10.1). OBJECT_VALUE means the object as a whole. This is one example of its use. You can also call a PL/SQL function with OBJECT_VALUE as the datatype of an IN formal parameter.

So you can just do:

BEGIN

    DBMS_OUTPUT.PUT_LINE('Profit percentage: ' || :NEW.OBJECT_VALUE.getProfit());

END;
/