Trigger for hr_employee table

CREATE OR REPLACE TRIGGER TRG_synch_hpms_doctor
after update or delete of emp_name, job_id on hr_employee
for each row
declare

    /*
    Purpose : This trigger synchronize the Doctor Name and Designation between HR_EMPLOYEE and HPMS_DOCTOR
    Created By: Kazi Mokarem;
    Date: 20/03/2011
    */
   
    cursor cur_hpms_doctor is
        select doctor_no,doctor_name,designation
        from hpms_doctor
        where doctor_no   = :new.emp_no
        ;
     crDoctor cur_hpms_doctor%rowtype;
     
    cursor cur_dr_desig is
       select job_desc,job_type_id
       from hr_jobcode
       where job_id = :new.job_id
       and rownum=1;
     
      vDesignation    hr_jobcode.job_desc%type;
      vJobTypeId      hr_jobtype.job_type_id%type;
 
   cursor cur_hr_job_type(cpJobTypeId  hr_jobtype.job_type_id%type)is
          select job_short_desc
          from   hr_jobtype
          where  job_type_id =cpJobTypeId
          and rownum=1;
    vJobShortDesc  hr_jobtype.job_short_desc%type;
    vActiveStatus varchar2(1);      
begin
 
  if updating then
 
         Begin
         update hpms_doctor
          set ACTIVE_FLAG=nvl(:New.active_status,:old.active_status)
          where doctor_no =nvl(:new.emp_no,:old.emp_no);
         exception when others then
         null;
         end;
      if :new.emp_name = :old.emp_name and :new.job_id = :old.job_id then
        return;
      end if;
     
           
      open cur_hpms_doctor;fetch cur_hpms_doctor into crDoctor;
 
      if cur_hpms_doctor%found then
     
          /*
          if :new.job_id is null then
             delete from hpms_doctor
             where doctor_no = :new.emp_no;
             return;
          end if;
          */
         
          if :new.job_id <> :old.job_id then
            open cur_dr_desig;fetch cur_dr_desig into vDesignation,vJobTypeId;close  cur_dr_desig;
          end if;
         
       
         
          /*
          if :new.job_id is not null then
           open  cur_hr_job_type(vJobTypeId);fetch  cur_hr_job_type into vJobShortDesc; close    cur_hr_job_type;
           if  nvl(vJobShortDesc,'#') <> 'DOC' then
             delete from hpms_doctor
             where doctor_no = :new.emp_no;
             return;
           end if;  
          end if;
          */
         
         
          if upper(crDoctor.doctor_name) <> upper(nvl(:new.emp_name,:old.emp_name))
             and upper(crDoctor.designation) <> upper(vDesignation) then
           
             update hpms_doctor
             set doctor_name = nvl(:new.emp_name,:old.emp_name),
                 designation = vDesignation
             where doctor_no = nvl(:new.emp_no,:old.emp_no);
           
          elsif upper(crDoctor.doctor_name) <> upper(nvl(:new.emp_name,:old.emp_name)) then
             update hpms_doctor
             set    doctor_name = nvl(:new.emp_name,:old.emp_name)
             where  doctor_no   = nvl(:new.emp_no,:old.emp_no);  
          elsif upper(crDoctor.designation) <> upper(vDesignation)      then
             update hpms_doctor
             set designation = vDesignation
             where doctor_no = nvl(:new.emp_no,:old.emp_no);
          end if;
         
       --if upper(crDoctor.active_flag)<>upper(nvl(:New.active_status,:old.active_status)) then
          update hpms_doctor
          set ACTIVE_FLAG=nvl(:New.active_status,:old.active_status)--vActiveStatus
          where doctor_no =nvl(:new.emp_no,:old.emp_no);
        --end if;
         
         
      end if;  
      close cur_hpms_doctor;
  elsif deleting then
      --delete from hpms_doctor
      --where doctor_no = nvl(:new.emp_no,:old.emp_no);
      null;
  end if;
 
  exception
    when others then null;
 end;
/