在 ORACLE 中添加新值后更新列

Question

我在创建一个触发器时遇到了一些麻烦，该触发器将获得一个人的当前积分，请检查他将获得什么奖励，然后更新他的奖励头衔。所以这是场景：

PersonTable
| Person (PK) | RewardTitle (FK) | Score|
| A           | Beginner         | 0    |
| B           | MidRange         | 17   |
| C           | Professional     | 32   |
RewardTable
| RewardTitle (PK) | BottomScore | UpperScore| Reward        |
| Beginner         | 0           | 9         | Free food     |
| MidRange         | 10          | 19        | Free games    |
| Professional     | 20          | 100       | Unlimited time|

情况是，我想更新人A，以便他现在有11分，然后找出他的新积分属于哪个奖励头衔，并相应地更改他的奖励头衔，所以由于他有11分，他的奖励头衔现在将是中档。

我试图创建一个触发器，试图找到他所属的 RewardTitle，然后相应地更新此值，但我不断收到一条错误消息：

Person is mutating, trigger/function may not see it

我理解这个问题，但我想不出任何其他方法来达到结果。我试过使用

pragma autonomous_transaction;

但它仍然没有奏效，这是我在下面的尝试：

UPDATE PersonTable
SET Score = 11
WHERE Person = 'A';
DELIMITER //
CREATE OR REPLACE TRIGGER person_upd_trigger
AFTER UPDATE ON PersonTable
FOR EACH ROW
DECLARE
temp_variable VARCHAR(120);
BEGIN
SELECT r.RewardTitle INTO temp_variable
FROM PersonTable p, RewardTable r
WHERE :NEW.Person = p.Person
AND p.Score BETWEEN r.BottomScore AND r.UpperScore;
UPDATE PersonTable
SET RewardTitle = temp_variable
WHERE :NEW.Person = Person;
END;

我将非常感谢我能得到的所有帮助，我对Oracle SQL很陌生。谢谢

Answer 1

您无法在 BEFORE 触发器中更新已经更新的同一表(如果允许会导致无限循环)。相反，您需要将触发器重写为：

为了使其正常工作，您应该将触发器定义更改为BEFORE

CREATE OR REPLACE TRIGGER person_upd_trigger
BEFORE UPDATE ON PersonTable -- declare it as BEFORE
FOR EACH ROW
DECLARE
temp_variable VARCHAR(120);
BEGIN
SELECT r.RewardTitle INTO temp_variable
FROM RewardTable r
WHERE :NEW.Score BETWEEN r.BottomScore AND r.UpperScore;
-- here you are setting the new value, no need for an update.
:NEW.RewardTitle := temp_variable;
END;