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?

score 0 · Accepted Answer · edited Jan 12 '19 at 07:56

0

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;

edited Jan 12 '19 at 07:56

marc_s

732,580
175
1,330
1,459

answered Jan 11 '19 at 23:48

Derviş Kayımbaşıoğlu

28,492
4
50
72

Good idea, but the `INSERT...SELECT` statements have issues. There is no `price` column in the `CURRENCY` table. Also there is no `WHERE` clause in the `SELECT`, so even if that worked that would insert many lines in `EXCHANGE_RATE`. – GMB Jan 12 '19 at 00:13
good catch, I forgot to add where clause. By the way, I believe that he wants multiple inserts. IF the currency table contains 3 currencies, when the insert is made, there would be 6 different currencies is needed to be inserted (as far as I understand) :) – Derviş Kayımbaşıoğlu Jan 12 '19 at 00:18
Sorry Simonare but you are mixing things a little. `NAME != p_price` ? Also, as I said, there is no `price` column in the `CURRENCY` table... I believe this code would not compile. – GMB Jan 12 '19 at 00:30

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

1 Answers1