Oracle PL/SQL/Trigger/Trigger Error

Материал из SQL эксперт
Перейти к: навигация, поиск

shows an illegal use of LOBs in a trigger.

   <source lang="sql">

SQL> SQL> CREATE TABLE lobdemo (

 2    key NUMBER,
 3    clob_col CLOB,
 4    blob_col BLOB);

Table created. SQL> SQL> CREATE OR REPLACE TRIGGER LOBTrigger

 2    BEFORE UPDATE ON lobdemo
 3    FOR EACH ROW
 4  DECLARE
 5    v_InitialString VARCHAR2(50) :=
 6      "LOBTrigger: ";
 7  BEGIN
10    DBMS_LOB.WRITE(:new.clob_col, LENGTH(v_InitialString),
11                   1, v_InitialString);
12  END LOBTrigger;
13  /

Trigger created. SQL> drop table lobdemo; Table dropped. SQL> SQL>

</source>
   
  


This trigger will raise an ORA-4091 mutating table error.

   <source lang="sql">

SQL> SQL> CREATE TABLE lecturer (

 2    id               NUMBER(5) PRIMARY KEY,
 3    first_name       VARCHAR2(20),
 4    last_name        VARCHAR2(20),
 5    major            VARCHAR2(30),
 6    current_credits  NUMBER(3)
 7    );

Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)

 2                VALUES (10002, "Mar", "Wells","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10004, "Man", "Kyte","Economics", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10005, "Pat", "Poll","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10006, "Tim", "Viper","History", 4);

1 row created. SQL> SQL> SQL> CREATE OR REPLACE TRIGGER LimitMajors

 2    BEFORE INSERT OR UPDATE OF major ON lecturer
 3    FOR EACH ROW
 4  DECLARE
 5    studentMax CONSTANT NUMBER := 5;
 6    studentCount NUMBER;
 7  BEGIN
 8    SELECT COUNT(*)
 9      INTO studentCount
10      FROM lecturer
11      WHERE major = :new.major;
12
13    IF studentCount + 1 > studentMax THEN
14      RAISE_APPLICATION_ERROR(-20000,
15        "Too many lecturer in major " || :new.major);
16    END IF;
17  END LimitMajors;
18  /

Trigger created. SQL> SQL> UPDATE lecturer

 2    SET major = "History"
 3    WHERE ID = 10003;

UPDATE lecturer

      *

ERROR at line 1: ORA-04091: table sqle.LECTURER is mutating, trigger/function may not see it ORA-06512: at "sqle.LIMITMAJORS", line 5 ORA-04088: error during execution of trigger "sqle.LIMITMAJORS"

SQL> SQL> DROP TRIGGER LimitMajors; Trigger dropped. SQL> SQL> drop table lecturer; Table dropped.

</source>