Oracle PL/SQL/Trigger/Trigger Error
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>