sql中的触发器:突变触发器问题

Question

在尝试表中插入行时，我想将行插入另一个名为excercise_scores的表中。一次锻炼有多次尝试，根据评分方法（可以是最新分数、所有尝试的平均值或所有尝试的最高分数），我必须在锻炼_核心表中插入分数
因此，在将分数插入到excercise_score中时，我还必须检查锻炼的评分方法是什么，根据该方法从尝试表中获得分数，然后最终插入到excorcise_scores表中。

为此，我创建了一个SQL触发器，如下

CREATE OR REPLACE TRIGGER scores_trigger AFTER
 INSERT
  ON attempts FOR EACH row DECLARE P1 VARCHAR2(50);
 SCORES FLOAT;
 v_exists VARCHAR2(1) := 'F';
 BEGIN
  BEGIN
   SELECT
    'T'
     INTO
     v_exists
   FROM
     excercise_scores
   WHERE
    user_id        = :NEW.USER_ID
    AND EXCERCISE_ID = :NEW.EXCERCISE_ID
    AND COURSE_ID    = :NEW.COURSE_ID;
   EXCEPTION
    WHEN no_data_found THEN
    NULL;
  END;
 SELECT
  SELECTION_METHOD
 INTO
  P1
 FROM
  EXCERCISES
 WHERE
  EXCERCISE_ID = :NEW.EXCERCISE_ID
 AND COURSE_ID  = :NEW.COURSE_ID;
 IF (P1         = 'Latest') THEN
   SCORES      := :NEW.TOTAL_POINTS;
 ELSE
   IF (P1 = 'best') THEN
     SELECT
       MAX(TOTAL_POINTS)
     INTO
       SCORES
     FROM
      ATTEMPTS
     WHERE
      EXCERCISE_ID = :NEW.EXCERCISE_ID
     AND COURSE_ID  = :NEW.COURSE_ID
     AND USER_ID    = :NEW.USER_ID;         
   ELSE
     SELECT
       AVG(TOTAL_POINTS)
     INTO
       SCORES
     FROM
       ATTEMPTS
     WHERE
       EXCERCISE_ID = :NEW.EXCERCISE_ID
     AND COURSE_ID  = :NEW.COURSE_ID
     AND USER_ID    = :NEW.USER_ID;
   END IF;
 END IF;
 IF v_exists = 'T' THEN
   UPDATE
     EXCERCISE_SCORES
   SET
     TOTAL_POINTS = SCORES
   WHERE
     user_id        = :NEW.USER_ID
   AND EXCERCISE_ID = :NEW.EXCERCISE_ID
   AND COURSE_ID    = :NEW.COURSE_ID;
 ELSE
   INSERT
   INTO
     EXCERCISE_SCORES VALUES
     (
       :NEW.EXCERCISE_ID,
       :NEW.COURSE_ID,
       :NEW.COURSE_ID,
       SCORES
      ) ;
 END IF;
END;

但是当我尝试执行这个触发器时，它会给我以下错误：

Error starting at line 47 in command:
insert into attempts values(21,0,'vshesha',3,'CSC540',TO_DATE('20141014', 'YYYYMMDD'),12,6)
Error report:
SQL Error: ORA-04091: table VSHESHA.ATTEMPTS is mutating, trigger/function may not see it
ORA-06512: at "VSHESHA.SCORES_TRIGGER", line 18
ORA-04088: error during execution of trigger 'VSHESHA.SCORES_TRIGGER'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
       this statement) attempted to look at (or modify) a table that was
       in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

我不明白为什么它会出现这个错误，因为我在插入后运行触发器。通常情况下，在插入触发器之后不应该出现更改表的问题。

有人能帮我吗。我已经受够了很长一段时间了。

Answer 1

错误消息非常自我解释，导致错误的SQL语句是：

     SELECT
       AVG(TOTAL_POINTS)
     INTO
       SCORES
     FROM
       ATTEMPTS
     WHERE
       EXCERCISE_ID = :NEW.EXCERCISE_ID
     AND COURSE_ID  = :NEW.COURSE_ID
     AND USER_ID    = :NEW.USER_ID;

您正在从ATTEMPTS中进行选择，这就是当前正在更改的表。没有简单的解决方案——最明智的方法是使用将业务逻辑从触发器中移出并转移到应用程序代码中（请参阅AskTom关于更改表错误的内容）。

BTW：你说的"一般来说，在插入触发器之后不应该出现变异表问题"是完全错误的。请参阅数据库期刊文章以获得更长的解释（简而言之：总是遇到多行插入的可变表错误，在中，几乎所有都会遇到单行插入的错误）。

Answer 2

在Oralce中，您可以使用COMPOUND TRIGGER。在你的情况下，它将类似于这个。我没有把你所有的操作都复制到这个例子中，但你应该了解原理。

然而，正如其他答复已经提到的那样。首选的方法应该是将所有这些逻辑移动到PL/SQL过程中。

CREATE OR REPLACE TRIGGER scores_trigger
    FOR INSERT ON attempts
    COMPOUND TRIGGER
    TYPE RowTableType IS TABLE OF ROWID;
    RowTable RowTableType;
    aRow attempts%ROWTYPE;
    SCORES FLOAT;
    v_exists VARCHAR2(1) := 'F';
    BEFORE STATEMENT IS
    BEGIN
        RowTable := RowTableType();
    END BEFORE STATEMENT;
    AFTER EACH ROW IS
    BEGIN
        RowTable.EXTEND;
        RowTable(RowTable.LAST) := :NEW.ROWID;
    END AFTER EACH ROW;
    AFTER STATEMENT IS
    BEGIN
        FOR i IN RowTable.FIRST..RowTable.LAST LOOP
            SELECT * 
            INTO aRow
            FROM attempts
            WHERE ROWID = RowTable(i);
            SELECT SELECTION_METHOD
            INTO P1
            FROM EXCERCISES
            WHERE EXCERCISE_ID = aRow.EXCERCISE_ID
                AND COURSE_ID  = aRow.COURSE_ID;
            IF P1 = 'Latest' THEN
                SCORES := :NEW.TOTAL_POINTS;
            ELSE
                IF (P1 = 'best') THEN
                    SELECT MAX(TOTAL_POINTS)
                    INTO SCORES
                    FROM ATTEMPTS
                    WHERE EXCERCISE_ID = aRow.EXCERCISE_ID
                        AND COURSE_ID  = aRow.COURSE_ID
                        AND USER_ID    = aRow.USER_ID;         
                ELSE
                    SELECT AVG(TOTAL_POINTS)
                    INTO SCORES
                    FROM ATTEMPTS
                    WHERE EXCERCISE_ID = aRow.EXCERCISE_ID
                        AND COURSE_ID  = aRow.COURSE_ID
                        AND USER_ID    = aRow.USER_ID;
                END IF;
            END IF;
        END LOOP;
    END AFTER STATEMENT;
END scores_trigger;

PL/SQL过程的一个例子是：

CREATE OR REPLACE PROCEDURE insert_score(V_EXCERCISE IN SCORES.EXCERCISE_ID%TYPE, V_COURSE IN SCORES.COURSE_ID%TYPE, V_USER IN USER_ID%TYPE) IS
BEGIN
    INSERT INTO insert_score (EXCERCISE_ID, COURSE_ID, USER_ID) VALUES (V_EXCERCISE, V_COURSE, V_USER);
    BEGIN
    SELECT 'T'
    INTO v_exists
    FROM excercise_scores
    WHERE user_id        = :NEW.USER_ID
        AND EXCERCISE_ID = :NEW.EXCERCISE_ID
        AND COURSE_ID    = :NEW.COURSE_ID;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN;
    END;
    ... other stuff
END insert_score;

对于插入，您必须调用此过程，而不是直接执行插入。