Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Niraj Bhaskarbhai.

Asked: August 29, 2018 - 8:39 pm UTC

Last updated: November 29, 2018 - 12:32 am UTC

Version: Oracle 10

Viewed 1000+ times

You Asked

I am having issues with Trigger.
Trigger creation is successful but Once user try to test it, It is failing.
Before trigger creation, tester is able to add new email to email_t table. But Once I place trigger, they are getting this :

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


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

<code>
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;
/


Here is the script for ABC table where it is inserting data after firing of trigger :

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


If you can help me understand the issue and provide any solution, It will be really helpful.

Thank You,

and Connor said...

Your logic is wrong. If the cursor returns no rows, you'll see the error, eg

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


Much simpler to use a for loop and plsql do the cursor management

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.


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Can I use this instead ?

Niraj Bhaskarbhai Jani, August 31, 2018 - 6:12 pm UTC

If I use this instead of old trigger.
Do you think it will work ?


CREATE OR REPLACE TRIGGER EMAIL_MDM_IU_TRIGGER
BEFORE INSERT OR UPDATE
ON CUST_ADMIN.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);

BEGIN


SELECT ENTITY_ID INTO V_ENTITY_ID FROM ENTITY_ADDR_TYPE_LINK_T EATL
WHERE EATL.ADDR_SEQ_NBR = :Old.ADDR_SEQ_NBR;

SELECT ENTITY_TYPE_CD INTO V_ENTITY_TYPE_CD FROM ENTITY_ADDR_TYPE_LINK_T EATL
WHERE EATL.ADDR_SEQ_NBR = :Old.ADDR_SEQ_NBR;


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'
,NULL
,SYSDATE
,NULL
,'EMAIL_MDM_IU_TRIGGER');
END IF;
END;
/

Connor McDonald
September 04, 2018 - 5:18 am UTC

You can, but if you were planning on rewritting the trigger anyway, you'd look at doing away with the cursor altogether.

Can I use this instead ?

Niraj Bhaskarbhai Jani, August 31, 2018 - 6:13 pm UTC

If I use this instead of old trigger.
Do you think it will work ?


CREATE OR REPLACE TRIGGER EMAIL_MDM_IU_TRIGGER
BEFORE INSERT OR UPDATE
ON CUST_ADMIN.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);

BEGIN


SELECT ENTITY_ID INTO V_ENTITY_ID FROM ENTITY_ADDR_TYPE_LINK_T EATL
WHERE EATL.ADDR_SEQ_NBR = :Old.ADDR_SEQ_NBR;

SELECT ENTITY_TYPE_CD INTO V_ENTITY_TYPE_CD FROM ENTITY_ADDR_TYPE_LINK_T EATL
WHERE EATL.ADDR_SEQ_NBR = :Old.ADDR_SEQ_NBR;


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'
,NULL
,SYSDATE
,NULL
,'EMAIL_MDM_IU_TRIGGER');
END IF;
END;
/

turn into single sql and PLSQL Inquiry directives

Rajeshwaran, Jeyabal, September 03, 2018 - 11:47 am UTC

dont need to hard code the program name 'EMAIL_MDM_IU_TRIGGER' like this - instead they can be obtained dynamically with $$PLSQL_UNIT directives & also you can turn the above logic into a simple sql like this.

create or replace trigger email_mdm_iu_trigger
before insert or update on email_t
for each row
begin
 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 )
 select mdm_seq_nbr.nextval, entity_id,entity_type_cd,'C',
   'EMAIL_T',null,null,null,
   'N',sysdate,null,null,$$plsql_unit
  from ENTITY_ADDR_TYPE_LINK_T EATL 
  where EATL.ADDR_SEQ_NBR = :Old.ADDR_SEQ_NBR
  and :Old.Row_Updater_Nam != 'MDM_SERVICE_UPDT' ;  
end;
/  


Resolved

Niraj Bhaskarbhai Jani, September 26, 2018 - 4:53 pm UTC

Thank You everyone for helping.
Issue has been resolved.

Issue arises again

NJ, November 27, 2018 - 8:41 pm UTC

Hi,
I modified the trigger a bit which resolved the issue in past.
But Now there's a need again to modify it a bit. I tried to update the trigger but it is not working as expected with the new changes.
Trigger script:

CREATE OR REPLACE TRIGGER schema.EARL_MDM_IU_TRIGGER
BEFORE INSERT OR UPDATE
ON schema.ENTITY_TABLE
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
V_ID NUMBER;
BEGIN
SELECT MDM_SEQ_NBR.NEXTVAL INTO V_ID FROM dual;
IF UPDATING OR
:Old.Row_Updater_Nam NOT IN ('MDM_SERVICE_UPDT' , 'SFDC_SERVICE_UPDT' )
THEN
Insert Into schema.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 ,:Old.ENTITY_ID ,:Old.ENTITY_TYPE_CD ,'C' ,'ENTITY_TABLE' ,NULL
,NULL ,NULL ,'N' ,NULL ,SYSDATE , SYSDATE ,'EARL_MDM_IU_TRIGGER');

ELSE IF
Inserting OR
:New.Row_Updater_Nam NOT IN ( 'MDM_SERVICE_UPDT','SFDC_SERVICE_UPDT')
THEN
Insert Into schema.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 ,:New.ENTITY_ID ,:New.ENTITY_TYPE_CD ,'C' ,'ENTITY_TABLE' ,NULL
,NULL ,NULL ,'N' ,NULL ,SYSDATE , SYSDATE ,'EARL_MDM_IU_TRIGGER');

END IF;
END IF;
END;

My ultimate goal is to insert/update record,fire trigger, and add record in abc table when row_updater_nam column is not MDM_SERVICE_UPDT and/or SFDC_SERVICE_UPDT. But when i use above script, It is firing the trigger no matter what the value is for ROW_UPDATER_NAM column. I tried:
IF UPDATING OR
:Old.Row_Updater_Nam != 'MDM_SERVICE_UPDT' OR :Old.Row_Updater_Nam != 'SFDC_SERVICE_UPDT'
But same result.

Can anyone help me get my logic correct please?
What I want is, it should not fire trigger when value of row_updater_nam is either MDM_SERVICE_UPDT or SFDC_SERVICE_UPDT.

I mentioned table structure of abc table in the very first message of mine in this conversation.

Here is the table structure for ENTITY_TABLE:

CREATE TABLE schema.ENTITY_TABLE
(
ENTITY_ID VARCHAR2(20 BYTE) NOT NULL,
ENTITY_TYPE_CD VARCHAR2(5 BYTE) NOT NULL,
ADDR_ROLE_CD VARCHAR2(10 BYTE) NOT NULL,
ADDR_SEQ_NBR NUMBER(10) NOT NULL,
ADDR_TYPE_CD VARCHAR2(5 BYTE),
EFFEC_DT DATE,
INACTIVE_DT DATE,
ADDR_LINK_INACTIVE_RSN_CD VARCHAR2(5 BYTE),
PRIMARY_ADDR_IND VARCHAR2(1 BYTE),
PRIMARY_IND_LAST_CHG_DT DATE,
THIRD_PARTY_BILLING_IND VARCHAR2(1 BYTE),
ROW_INSERT_DT DATE,
ROW_UPDATE_DT DATE,
ROW_UPDATER_NAM VARCHAR2(50 BYTE);

Here's what I am using for testing :

SELECT *
FROM ENTITY_TABLE
where addr_seq_nbr = '100';

UPDATE ENTITY_TABLE
SET ROW_UPDATER_NAM = 'MDM_SERVICE_UPDT'
WHERE ADDR_SEQ_NBR = '100'
AND ADDR_TYPE_CD = 'POST'
and addr_role_cd = 'PHYSICAL';

SELECT count(*)
FROM schema.abc
where entity_id = '994011355416' ;

Please help me understand what and where I am doing wrong.

Appreciate for your valuable suggestions.

Thank You,
Niraj
Connor McDonald
November 28, 2018 - 1:07 am UTC

The code:

IF UPDATING OR :Old.Row_Updater_Nam NOT IN ('MDM_SERVICE_UPDT' , 'SFDC_SERVICE_UPDT' )


will yield true when

- you are updating

OR

- Row_Updater_Nam NOT IN ('MDM_SERVICE_UPDT' , 'SFDC_SERVICE_UPDT' )

So *any* time you update, this comes out as true. Same with the insert.

I suspect you want:

IF UPDATING AND :Old.Row_Updater_Nam NOT IN ('MDM_SERVICE_UPDT' , 'SFDC_SERVICE_UPDT' )

To Connor

Niraj Jani, November 28, 2018 - 4:47 pm UTC

I tried using :
IF UPDATING AND :Old.Row_Updater_Nam NOT IN ('MDM_SERVICE_UPDT' , 'SFDC_SERVICE_UPDT' )

and still it does the same thing.
But this time it's very random. I mean, if I run update statement with MDM_SERVICE_UPDT/SFDC_SERVICE_UPDT, it sometime fires trigger and sometime not. So I am not sure why it is doing that.
I think my Trigger Script is not that accurate.
Can you Help me understand the issue with that Please ?
Thank You
Connor McDonald
November 29, 2018 - 12:32 am UTC

We would need a top to bottom test case then, with some real data.

If that's difficult, then simplify your trigger just down to the basic elements of when you want it to fire etc. Add some dbms_output calls and work through some data examples and see where things are not working.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library