sql - 如何在触发器中使用更新表中的数据？

Question

所以我有这两张表：

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)
);

每当我插入一个新的CURRENCY，我想EXCHANGE_RATES在所有以前CURRIENCIES的和一个新的之间创建一个新的。假设我的表中只有一种货币：

INSERT INTO CURRENCY VALUES("EURO", 2.0)

现在要插入一个新的：

INSERT INTO CURRENCY VALUES("DOLLAR", 1.0)

我的目标是插入EXCHANGE_RATE行：

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

最好的方法是什么？我试过使用AFTER INSERT OR UPDATE 触发器：

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;
/

但它不起作用，因为我必须查询CURRENCY表，正在更新，我最终得到 ORA-04091。

这样做的正确方法是什么？

标签： sqloracleplsqldatabase-triggerora-04091

Answer 1

您无法查询导致触发器在触发器本身内部触发的表。

如果我是你，我会考虑使用存储过程的方法。只需将您的插入脚本移动到一个过程中

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;