Trigger-Cursor example

CREATE OR REPLACE TRIGGER LOG_AUDIT_TRIGGER AFTER
  INSERT OR UPDATE ON DEV2_APP.CASES   --table name on which u want to fire a trigger
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
    --WHEN (new.case_status_code<>old.case_status_code)
  DECLARE                                                            -- variable declaration
    n_status VARCHAR2(5) ;
    o_status varchar2(5);
  --n_status_reason varchar2(50) ;
  n_modified_date DATE;
  n_modified_user VARCHAR2(20);
  o_caseId          VARCHAR2(20);
  l_changeid      NUMBER;
-- curser body or defination
  CURSOR c_getmax_changeid(caseid VARCHAR2,field_name VARCHAR2)
  IS
    SELECT MAX(CA.change_id) AS maxChangeId
    FROM DEV2_APP.case_audit_log CA
    WHERE CA.CASE_ID  =caseid
    AND CA.FIELD_NAME=field_name;
BEGIN     -- begin of trigger body or defination
  n_status:=:new.CASE_STATUS_CODE;
  o_status:=:old.CASE_STATUS_CODE;
  n_modified_date:=sysdate;
  n_modified_user:='SSK';
  o_caseId:=:old.CASE_ID;
  l_changeid:=0;
  IF n_status<>o_status THEN
  FOR rec IN c_getmax_changeid(o_caseId,'CASE_STATUS_CODE')
    LOOP
      l_changeid:=NVL(rec.maxChangeId,0);
    END LOOP;
    l_changeid:=l_changeid+1;
    INSERT
    INTO DEV2_APP.case_audit_log(AUDIT_LOG_ID,CASE_ID,CHANGE_ID,TABLE_NAME,FIELD_NAME,FIELD_VALUE,CREATED_USER,CREATED_DTTM,VERSION_ID)
      VALUES(case_audit_log_ID_Q.nextval,o_caseId,l_changeid,'CASES','CASE_STATUS_CODE',n_status,n_modified_user,n_modified_date,1);
  END IF;
END;