关于触发器的问题

Question

CREATE OR REPLACE TRIGGER "TIG_BUGNUMTORESULT" AFTER
INSERT
OR UPDATE ON "CHK_RESULT" FOR EACH ROW 
declare
    v_task_id    varchar2(20);
    v_bug_no     varchar2(20);
    v_bug_no_new   varchar2(20);    errmsg           char(200);
    no_num            integer;
    found            boolean;begin  if :new.bug_flag=1 then     v_task_id:= :new.task_id;     select max(bug_no)     into v_bug_no
     from chk_result
     where to_char(check_date,'yyyy')=to_char(sysdate,'yyyy') 
       and bug_flag=1 and task_id<>v_task_id;     if (v_bug_no is null ) or (v_bug_no='') then
     
       v_bug_no_new := to_char(sysdate,'yy')+'-'+'0001';
     else      
       v_bug_no_new := to_char(sysdate,'yy')+'-'+substr(to_char(10000+to_NUMBER(SUBSTR(v_bug_no,4,4))),2,4);
     end if;     update chk_result
     set bug_no=v_bug_no_new
     where task_id=v_task_id and bug_flag=1;
       end if;end;
以上是触发器的代码。
下面是触发的代码。update chk_result
set bug_flag=1
where task_id='1203112'
执行结果出错。ORA-04091 触发器/函数不能读
ORA-06512  line 16
ORA-04088  执行过程出错

Answer 1

你的意思是序号自动增加，不如写个过程或者函数来取序号的下一个值，然后直接update这样不是更好吗，效率也高。
  SELECT VALUE INTO V_SNO
  FROM SYSCFG
  WHERE SECTION = V_SECTION
        AND CODE = V_CODE;IF (LENGTH(V_SNO) > 4 OR V_SNO = 0 OR V_SNO ='9999') THEN
    V_SNO := 1;
  END IF;
  V_SNO := RIGHT('0000' || TO_CHAR(V_SNO)+1 ,4);  UPDATE SYSCFG
  SET VALUE = V_SNO
  WHERE SECTION = V_SECTION
        AND CODE = V_CODE;P_VALNO := to_char(sysdate,'yy')||RIGHT('0000'|| V_SNO, 4);

Answer 2

虽然是序列自动增加但是有条件的增加，
所以没有办法.
后改为，但是还是有问题CREATE OR REPLACE TRIGGER "TIG_BUGNUMTORESULT" AFTER
INSERT
OR UPDATE ON "CHK_RESULT" FOR EACH ROW 
declare
    v_task_id    varchar2(50);
    v_bug_no     varchar2(20);
    v_bug_no_new   varchar2(20);begin  if :new.bug_flag=1 then
  begin
     v_task_id:= :new.task_id;     select max(bug_no)
     into v_bug_no
     from chk_result
     where to_char(check_date,'yyyy')=to_char(sysdate,'yyyy') 
       and bug_flag=1 and task_id<>v_task_id ;     if (v_bug_no is null )  then
       v_bug_no_new := to_char(sysdate,'yy')||'-'||'0001';
     else      
       v_bug_no_new := to_char(sysdate,'yy')||'-'||substr(to_char(10001+to_NUMBER(SUBSTR(v_bug_no,4,4))),2,4);
     end if;     update chk_result
     set bug_no=v_bug_no_new
     where task_id=v_task_id and bug_flag=1;
     
  end;
  end if;end;

Answer 3

    if (v_bug_no is null ) or (v_bug_no='') then 
    
      v_bug_no_new := to_char(sysdate,'yy')+'-'+'0001'; 
    else      
      v_bug_no_new := to_char(sysdate,'yy')+'-'+substr(to_char(10000+to_NUMBER(SUBSTR(v_bug_no,4,4))),2,4); 
    end if; 
改为    if (v_bug_no is null ) or (v_bug_no='') then 
    
      v_bug_no_new := to_char(sysdate,'yy') || '-' || '0001'; 
    else      
      v_bug_no_new := to_char(sysdate,'yy') || '-' || substr(to_char(10000+to_NUMBER(SUBSTR(v_bug_no,4,4))),2,4); 
    end if; 

Answer 4

try  to  use    commit

Answer 5

  触发器不能使用commit我发现这个问题的根本问题是，在触发器怎么样修改触发表上？

Answer 6

把 FOR EACH ROW  这个除掉 就是触发表上

Answer 7

这是变异表的问题，在FOR EACH ROW 模式下，触发表将会是变异表，不能查询与更改
可以创建一个包作为数据存储，创建两个触发器，一个为语句级，一个行级
在行级触发器记录 :new里的值，但不查询变异表，而在语句级的触发器进行查询和更新表。有兴趣可参照下面的文章代码
6.3.1 变异表案例介绍
假设我们要把每个专业的学生名额限制在五个。我们可以通过使用表s t u d e n g t s上的之前插入
或更新行级触发器来实现这种限制，下面是该触发器的代码：
节选自在线代码LimitMajors .sql
CREATE OR REPLACE TRIGGER LimitMajors
/* Limits the number of students in each major to 5.
If this limit is exceeded, an error is raised through
raise_application_error. */
BEFORE INSERT OR UPDATE OF major ON students
FOR EACH ROW
DECLARE
v_MaxStudents CONSTANT NUMBER := 5;
v_CurrentStudents NUMBER;
BEGIN
-- Determine the current number of students in this
-- major.
SELECT COUNT(*)
INTO v_CurrentStudents
FROM students
WHERE major = :new.major;
-- If there isn't room, raise an error.
IF v_CurrentStudents + 1 > v_MaxStudents THEN
RAISE_APPLICATION_ERROR(-20000,
'Too many students in major ' || :new.major);
END IF;
END LimitMajors;
初看，该触发器好象可以实现需要的功能。然而，如果我们更新表s t u d e n t s并激活该触发器，
我们会得到下面的输出：
节选自在线代码LimitMajors .sql
SQL> UPDATE students
2 SET major = 'History'
3 WHERE ID = 10003;
UPDATE students
*
ERROR at line 1:
ORA-04091: table EXAMPLE.STUDENTS is mutating, trigger/function
may not see it
ORA-06512: at line 7
ORA-04088: error during execution of trigger 'EXAMPLE.LIMITMAJORS'
由于触发器L i m i t M a j o r查询其自己的触发表（该表是变异表），所以导致了错误O R A - 4 0 9 1。
另外，错误O R A - 4 0 9 1是在该触发器激活时引发的，而不是在创建时引发的。
表s t u d e n t s由于使用了行级触发器而成了变异表，这就使我们不能在该行级触发器中对该表
进行查询，而只能在语句级触发器使用查询语句。然而，我们还不能只是简单地把触发器
L i m i t M a j o r修改为语句级触发器，这是因为我们需要在触发器体中使用: n e w. m a j o r的值。解决该
问题的方法是创建两个触发器，即一个行级触发器和一个语句级触发器。在行级触发器中，我
们记录: n e w. m a j o r的值，但我们不查询表s t u d e n t s；而查询任务由语句级触发器来实现并使用行
触发器记录的值。
至于如何记录: n e w. m a j o r的值。一种方法是在包的内部使用P L / S Q L表来记录。用这种方法，
我们可以在每次更新时保存多个值。同样，每个会话也得到其自己打包变量的实例，因此，我
们就不必担心由于不同会话进行同时更新操作而引起的麻烦。实现上述方案的包s t u d e n t _ d a t a ,以
及改进的触发器R L i m i t M a j o r s和S L i m i M a j o r s的程序如下：
节选自在线代码mutating .sql
CREATE OR REPLACE PACKAGE StudentData AS
TYPE t_Majors IS TABLE OF students.major%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_IDs IS TABLE OF students.ID%TYPE
INDEX BY BINARY_INTEGER;
v_StudentMajors t_Majors;
v_StudentIDs t_IDs;
v_NumEntries BINARY_INTEGER := 0;
END StudentData;
CREATE OR REPLACE TRIGGER RLimitMajors
BEFORE INSERT OR UPDATE OF major ON students
FOR EACH ROW
BEGIN
/* Record the new data in StudentData. We don't make any
changes to students, to avoid the ORA-4091 error. */
StudentData.v_NumEntries := StudentData.v_NumEntries + 1;
StudentData.v_StudentMajors(StudentData.v_NumEntries) :=
:new.major;
StudentData.v_StudentIDs(StudentData.v_NumEntries) := :new.id;
END RLimitMajors;
CREATE OR REPLACE TRIGGER SLimitMajors
AFTER INSERT OR UPDATE OF major ON students
DECLARE
v_MaxStudents CONSTANT NUMBER := 5;
v_CurrentStudents NUMBER;
v_StudentID students.ID%TYPE;
v_Major students.major%TYPE;
BEGIN
/* Loop through each student inserted or updated, and verify
that we are still within the limit. */
FOR v_LoopIndex IN 1..StudentData.v_NumEntries LOOP
v_StudentID := StudentData.v_StudentIDs(v_LoopIndex);
v_Major := StudentData.v_StudentMajors(v_LoopIndex);
-- Determine the current number of students in this major.
SELECT COUNT(*)
INTO v_CurrentStudents
FROM students
WHERE major = v_Major;
-- If there isn't room, raise an error.
IF v_CurrentStudents > v_MaxStudents THEN
RAISE_APPLICATION_ERROR(-20000,
'Too many students for major ' || v_Major ||
' because of student ' || v_StudentID);
END IF;
END LOOP;
-- Reset the counter so the next execution will use new data.
StudentData.v_NumEntries := 0;
END LimitMajors;
注意请注意要在运行前面的脚本之前，一定要撤消不正确的L i m i t M a j o r s触发器。
现在，我们可以通过更新表s t u d e n t来测试这一系列触发器直到我们的程序中出现了过多的
历史专业为止
节选自在线代码mutating .sql
SQL> UPDATE students
2 SET major = 'History'
3 WHERE ID = 10003;
1 row updated.
SQL> UPDATE students
2 SET major = 'History'
3 WHERE ID = 10002;
1 row updated.
SQL> UPDATE students
2 SET major = 'History'
3 WHERE ID = 10009;
UPDATE students
*
ERROR at line 1:
ORA-20000: Too many students for major History because of student 10009
ORA-06512: at "EXAMPLE.SLIMITMAJORS", line 19
ORA-04088: error during execution of trigger 'EXAMPLE.SLIMITMAJORS'
上面的结果就是我们期望的功能。当行级触发器读入或修改变异表（ mutating table ）时，
上述技术可以引发错误信息O R A - 4 0 9 1。为了避免在行级触发器中进行非法处理，我们把该处理
推迟到一个之后的语句级触发器实现，这样一来该处理就是合法的了。打包的P L / S Q L表是用来
存储被修改的行的。
下面是应用该技术时要注意的几个问题：
• 由于P L / S Q L表是位于包中，所以这些表对于行级触发器和语句级触发器都是可见的，确
保变量的全局性的唯一方法是把要定义的全局变量放在包中。
• 我们在该程序中使用了计数器变量S t u d e n t s D a t a . v _ N u m E n t r i e s，当其所在的包首次创建时，
该变量的初始值为０。然后，该变量的值由行级触发器修改。语句级触发器对该变量进行
引用并在处理结束后将该变量设置为０。上述的步骤是必须的，因为只有这样该会话发布
的U P D AT E语句将实现正确的结果。
• 对触发器S L i m i t M a j o r s进行最多学生数量的检查将稍有变化。由于该触发器是语句之后触
发器，所以变量v _ C u r r e n t S t u d e n t s将在执行插入或更新命令后保存某专业的学生数量。因
此，在触发器L i m i t M a j o r中对变量v_CurrentStudents+1 的检查也被变量v _ C u r r e n t S t u d e n t s
代替。
• 我们也可以使用数据库表来代替P L / S Q L表。我个人不喜欢这种技术，这是由于发布
U P D AT E的多个同时会话之间可能有相互用（在O r a c l e 8 i中，我们可以使用临时表）。打包
的P L / S Q L表是众多会话之间唯一的，因此解决了上面的问题。

Answer 8

太乱了，给你书名吧
《Oracle_8i_PL_SQL高级程序设计》
第6章数据库触发器

Answer 9

《Oracle_8i_PL_SQL高级程序设计》
第6章数据库触发器
下来仔细看看，没有讲到这些

Answer 10

http://www.itpub.net/thread-1032067-1-1.html