CODEWITHSUNDEEP
oracle-databasefor-loopplsqlcursordatabase-trigger
cHaNkX
cHaNkX

Reputation: 35

How can I use PL/SQL Cursor for loop for a Trigger

Hi I'm a newbie to plsql this is the first time I use plsql

I created trigger using plsql. Here is the syntax that I use to create that trigger. but it giving an error as "[Err] ORA-24344: success with compilation error" I cant figure out where I went wrong. In this trigger I use a for loop with a cursor. I think something wrong with that cursor Can anyone help me to figure out where I went wrong . I use Navicat to do this. I'm struggling with this for almost 5 days :( thanks in advance 

CREATE OR REPLACE TRIGGER "c" 
  AFTER INSERT ON "EMP_REPORT_TO"
  REFERENCING OLD AS "OLD" NEW AS "NEW"
  FOR EACH ROW
DECLARE

miclaim_supervisor_count number;
employee_company_code number;
employee_businessunit number;

cursor  projMgrsCursor is select b.BU_MEMBER_ID
from BU_MEMBER b, EMP_SUB_DIV s
where s.EMP_NO = :NEW.EMP_NO
and s.SUB_DIVISION_CODE = '02' and s.DIV_CODE = '041'
and b.BU_ID IN (select BU_ID from BU_MEMBER where BU_MEMBER_ID = :NEW.EMP_NO);


BEGIN
        delete from MICL_SUPERVISORS where EMP_NO = :NEW.EMP_NO and IS_OVVERRIDDEN = 0;
        select count(*) into miclaim_supervisor_count from MICL_SUPERVISORS where EMP_NO = :NEW.EMP_NO and IS_OVVERRIDDEN = 1;
        select COMPANY_CODE into employee_company_code from  EMPLOYEE_MASTER where EMP_NO = :NEW.EMP_NO;


if (employee_company_code = 'SOFT')then 

            OPEN projMgrsCursor;

            FOR projMgrsCursor IN projMgrs 
            LOOP                
            insert into MICL_SUPERVISORS VALUES ((:NEW.ID), (SELECT SYSDATE FROM DUAL), :NEW.ENTRYADDEDBY_EMP_NO, 3000, 0, projMgrEmpNo, NULL,:NEW.EMP_NO);
            END LOOP;   
        close projMgrsCursor;

else
            if(miclaim_supervisor_count IS NULL or miclaim_supervisor_count<1) then
                insert into MICL_SUPERVISORS VALUES ((:NEW.ID), (SELECT SYSDATE `enter code here`FROM DUAL), :NEW.ENTRYADDEDBY_EMP_NO, 3000, 0, :NEW.SUP_EMP_NO, NULL,:NEW.EMP_NO);
            end if;
end if;


END;
;

Upvotes: 0

Views: 13980

Answers (2)

cHaNkX
cHaNkX

Reputation: 35

Thanks for all ur answers and time :)

well I figured out where I went wrong thanks to oracle sql developer actually something wrong with for loop

here is code (corrected code for loop)

OPEN  projMgrsCursor;

    LOOP
    FETCH projMgrsCursor INTO projMgrs;
    EXIT WHEN projMgrsCursor%NOTFOUND;
    insert into
 MICL_SUPERVISORS VALUES ((:NEW.ID), (SELECT SYSDATE FROM DUAL), :NEW.ENTRYADDEDBY_EMP_NO, 3000, 0,projMgrs, NULL,:NEW.EMP_NO);
    END LOOP;   

  CLOSE projMgrsCursor;

Hope that this will help to anyone like me :)

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52376

No need for a cursor at all -- why not just use a SQL statement to perform the insert?

Upvotes: 0

Related Questions