в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:58:30Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Trigger/Trigger Error - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия