SQL & PL/SQL

I have a trigger on a table which triggers when the column "APPLICATION_OFFER_SENT" is updated! The trigger looks like this:

CREATE OR REPLACE TRIGGER SCHEMA.COPY_APPLICATIONS
AFTER UPDATE
OF APPLICATION_OFFER_SENT
ON PPLE_T_APPLICATION 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
    UPDATE_OR_INSERT_APPL(:old.PK_APPLICATION_NO);
END;

It just executes a procedure and takes the "PK_APPLICATION_NO" as its only parameter.

When i run the SP itdefl: exec UPDATE_OR_INSERT_APPL(1042); It works fine.
But when its done through the trigger it doesnt?



The Proc looks like this:

CREATE OR REPLACE PROCEDURE SCHEMA.UPDATE_OR_INSERT_APPL (IN_APPL_NO NUMBER) IS

CURSOR c1 IS
SELECT title, fullname, universityid, appl_no,  appl_no_hash, OFFER_EXPIRATION_DATE, APPLICATION_CANCEL_DATE, room_hall 
  FROM SCHEMA.ACCOM_APPLICATION_VIEW
 WHERE appl_no = IN_APPL_NO;

rowcnt NUMBER;

 BEGIN
  FOR rec IN c1
      LOOP  
      SELECT COUNT(*) INTO rowcnt FROM SCHEMA2.ACCOMM_OFFER_ACCEPTANCE a WHERE a.appl_no = IN_APPL_NO;
           
           IF rowcnt = 0  THEN
               INSERT INTO SCHEMA2.ACCOMM_OFFER_ACCEPTANCE (title, fullname, universityid, appl_no, appl_no_hash, offer_expiration_date, application_cancel_date, room_hall)
                    VALUES (rec.title, rec.fullname, rec.universityid, rec.appl_no, rec.appl_no_hash, rec.offer_expiration_date, rec.application_cancel_date, rec.room_hall);
                    COMMIT;
                    
                    
                    
           ELSIF rowcnt !=0 THEN
           
                UPDATE SCHEMA2.ACCOMM_OFFER_ACCEPTANCE t
                   SET (t.offer_expiration_date, t.application_cancel_date, t.room_hall) = (select OFFER_EXPIRATION_DATE, APPLICATION_CANCEL_DATE, ROOM_HALL from SCHEMA.ACCOM_APPLICATION_VIEW B
                                                                                                  where B.appl_no = IN_APPL_NO);        
          
           END IF;

      END LOOP; 
 END;

Edited by: oraCraft on 09-Sep-2010 09:11

Edited by: oraCraft on 09-Sep-2010 09:11