How to use data from the updated table in a trigger?

Question

So I have these two tables:

create table CURRENCY
(
     name VARCHAR2(40 CHAR) PRIMARY KEY,
     value NUMBER(6,2)
);

create table EXCHANGE_RATE
(
     currency1 references CURRENCY(name),
     currency2 references CURRENCY(name),
     price NUMBER(6,2),
     PRIMARY KEY(currency1, currency2)
);

Whenever I insert a new CURRENCY, I want to create new EXCHANGE_RATES between all previous CURRIENCIESand a new one. Let's say I have just one currency in my table:

INSERT INTO CURRENCY VALUES("EURO", 2.0)

And now want to insert a new one:

INSERT INTO CURRENCY VALUES("DOLLAR", 1.0)

My goal is to insert into EXCHANGE_RATE rows:

"EURO", "DOLLAR", 2.0
"DOLLAR", "EURO", 0.5

What is the best way to do is? I've tried to use AFTER INSERT OR UPDATE trigger:

CREATE OR REPLACE TRIGGER new_currency
AFTER INSERT OR UPDATE
ON CURRENCY
FOR EACH ROW

BEGIN

INSERT INTO EXCHANGE_RATE (currency1, currency2, price)
SELECT name, :new.name, price/:new.price
FROM CURRENCY;

INSERT INTO EXCHANGE_RATE (currency1, currency2, price)
SELECT  :new.name, name, :new.price/price
FROM CURRENCY;

END;
/

but it doesn't work since I have to query CURRENCY table, that is beeing updated and I just end up with ORA-04091.

What is the right way to do this?

Answer 1

You can't query the table that caused a trigger to fire inside the trigger itself.

If I were you, I will consider to use stored procedure approach. Just move your insert script into a procedure

CREATE PROCEDURE INS_CURRENCY (p_currency varchar2, p_price number) as
BEGIN
    INSERT INTO CURRENCY VALUES(p_currency , p_Price);

    INSERT INTO EXCHANGE_RATE (currency1, currency2, price)
    SELECT name, p_currency, price/p_price
    FROM CURRENCY
    WHERE NAME != p_current;

    INSERT INTO EXCHANGE_RATE (currency1, currency2, price)
    SELECT  p_currency, name, p_price/price
    FROM CURRENCY
    WHERE name != p_currency;
END;