Oracle 触发器创建问题

1) Exception Information
*********************************************
Exception Type: System.Web.Services.Protocols.SoapException
Actor: 
Lang: 
Node: 
Role: 
SubCode: NULL
Message: java.sql.SQLException: Insert Error:row count=0 status=-1001 error message=ORA-01001: invalid cursor
ORA-06512: at "EMAIL_MDM_IU_TRIGGER", line 40
ORA-04088: error during execution of trigger 'EMAIL_MDM_IU_TRIGGER' column=null table=EMAIL_T
Data: System.Collections.ListDictionaryInternal
TargetSite: System.Object[] ReadResponse(System.Web.Services.Protocols.SoapClientMessage, System.Net.WebResponse, System.IO.Stream, Boolean)
HelpLink: NULL
Source: System.Web.Services
<code> 


Here is the trigger script which I am using to create trigger : 


CREATE OR REPLACE TRIGGER EMAIL_MDM_IU_TRIGGER
BEFORE INSERT OR UPDATE
ON EMAIL_T
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
V_ID NUMBER; 
V_ENTITY_ID VARCHAR2(20); 
V_ENTITY_TYPE_CD VARCHAR2(5);

CURSOR C_ENTITY_LINK IS
           SELECT *
            FROM ENTITY_ADDR_TYPE_LINK_T EATL
            WHERE :Old.ADDR_SEQ_NBR =  EATL.ADDR_SEQ_NBR;
R_ENTITY_LINK C_ENTITY_LINK%ROWTYPE;

BEGIN 
     OPEN C_ENTITY_LINK;
         FETCH C_ENTITY_LINK INTO R_ENTITY_LINK;

        BEGIN
           IF C_ENTITY_LINK%NOTFOUND
             THEN
               CLOSE C_ENTITY_LINK;
           END IF;

          WHILE C_ENTITY_LINK%FOUND
          LOOP 
 
    SELECT MDM_SEQ_NBR.NEXTVAL INTO V_ID FROM DUAL; 
IF 
        :Old.Row_Updater_Nam != 'MDM_SERVICE_UPDT' 
    THEN

    Insert Into ABC
                      ( MDM_IUD_SEQ_NBR
                       ,ENTITY_ID
                       ,ENTITY_TYPE_CD
                       ,DATA_CHANGE_CD
                       ,TABLE_NAME
                       ,PRIMARY_KEY_COL
                       ,SECONDARY_KEY_COL
                       ,PRIMARY_KEY_VAL
                       ,ROW_PROCESS_CD
                       ,ERROR_MSG
                       ,ROW_INSERT_DT
                       ,ROW_UPDATE_DT
                       ,ROW_UPDATER_NAM)
                Values ( V_ID
                       ,V_ENTITY_ID
                       ,V_ENTITY_TYPE_CD
                       ,'C'
                       ,'EMAIL_T'
                       ,NULL
                       ,NULL
                       ,NULL
                       ,'N'
                       ,SYSDATE
                       ,NULL
                       ,NULL
                       ,'EMAIL_MDM_IU_TRIGGER'); 
END IF; 
     END LOOP;
      CLOSE C_ENTITY_LINK;
          
         
             END;
END;
/
复制

CREATE TABLE ABC
(
  MDM_IUD_SEQ_NBR    NUMBER(20)                 NOT NULL,
  ENTITY_ID          VARCHAR2(20 BYTE)          NOT NULL,
  ENTITY_TYPE_CD     VARCHAR2(5 BYTE)           NOT NULL,
  DATA_CHANGE_CD     VARCHAR2(5 BYTE)           NOT NULL,
  TABLE_NAME         VARCHAR2(100 BYTE)         NOT NULL,
  PRIMARY_KEY_COL    VARCHAR2(50 BYTE),
  SECONDARY_KEY_COL  VARCHAR2(50 BYTE),
  PRIMARY_KEY_VAL    VARCHAR2(50 BYTE),
  ROW_PROCESS_CD     VARCHAR2(1 BYTE)           NOT NULL,
  ERROR_MSG          VARCHAR2(256 BYTE),
  ROW_INSERT_DT      DATE                       NOT NULL,
  ROW_UPDATE_DT      DATE,
  ROW_UPDATER_NAM    VARCHAR2(50 BYTE)
) 
复制

SQL> create table t as select rownum x from dual connect by level <= 5;

Table created.

SQL>
SQL> set serverout on
SQL> declare
  2    cursor c is select * from t where 1=0;   <<=== returns no rows
  3    v int;
  4  begin
  5    open c;
  6    fetch c into v;
  7    if c%notfound then
  8      close c;
  9    end if;
 10    while c%found loop
 11       dbms_output.put_line(v);
 12    end loop;
 13    close c;
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 10
复制

SQL> set serverout on
SQL> declare
  2    cursor c is select * from t where 1=0;
  3    v int;
  4  begin
  5    for i in c loop
  6       dbms_output.put_line(i.x);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> set serverout on
SQL> declare
  2    cursor c is select * from t;
  3    v int;
  4  begin
  5    for i in c loop
  6       dbms_output.put_line(i.x);
  7    end loop;
  8  end;
  9  /
1
2
3
4
5

PL/SQL procedure successfully completed.
复制