Transaction History Log Trigger

CREATE OR REPLACE TRIGGER JVDETAIL_LOG
  before insert or update or delete ON JVDETAIL
  for each row
declare
  oldvalue     varchar2(2000) := null;
  newvalue     varchar2(2000) := null;
  changedvalue long := null;
begin
  if inserting then
    newvalue := 'DATE=' || :NEW.JDATE || '; ' || 'REF NO=' || :NEW.REFNO || '; ' ||
                'PATIENT ID=' || :NEW.PATIENT_ID || '; ' ||
                'ADMISSION DATE=' || :NEW.ADMISSION_DATE || '; ' ||
                'ACCOUNT CODE=' || :NEW.ACCOUNT_CODE || '; ' || 'DEBIT=' ||
                :NEW.DEBIT || '; ' || 'CREDIT=' || :NEW.CREDIT || '; ' ||
                'NARRATION=' || :NEW.NARRATION || '; ' || 'QUANTITY=' ||
                :NEW.QUANTITY || '; ' || 'RATE=' || :NEW.RATE || '; ' ||
                'ITEM CODE=' || :NEW.ITEM_CODE || '; ' || 'VAT RATE=' ||
                :NEW.VAT_RATE || '; ' || 'DEPT=' || :NEW.DEPT || '; ' ||
                'ADMISSION NO=' || :NEW.ADMISSION_NO || '; ' || 'STATUS=' ||
                :NEW.STATUS || '; ' || 'UNIT=' || :NEW.UNIT || '; ' ||
                'BILL NO=' || :NEW.BILL_NO || '; ' || 'BILL DATE=' ||
                :NEW.BILL_DATE || '; ' || 'TR MODE=' || :NEW.TR_MODE || '; ' ||
                'TR NAME=' || :NEW.TR_NAME || '; ' || 'TR NO=' ||
                :NEW.NARRATION || '; ' || 'TR VALIDITY=' ||
                :NEW.TR_VALIDITY || '; ';
    insert into hisaudit
    values
      (USER,
       'JVDETAIL',
       'MR/VOUCHER DETAILS',
       SYSDATE,
       'I',
       :new.refno,
       newvalue);
  elsif updating then
    changedvalue := CHECK_VALUE('JDATE',
                                TO_CHAR(:OLD.JDATE),
                                TO_CHAR(:NEW.JDATE)) ||
                    CHECK_VALUE('REFNO', :OLD.REFNO, :NEW.REFNO) ||
                    CHECK_VALUE('PATIENT_ID',
                                :OLD.PATIENT_ID,
                                :NEW.PATIENT_ID) ||
                    CHECK_VALUE('ADMISSION_DATE',
                                :OLD.ADMISSION_DATE,
                                :NEW.ADMISSION_DATE) ||
                    CHECK_VALUE('ACCOUNT CODE',
                                :OLD.ACCOUNT_CODE,
                                :NEW.ACCOUNT_CODE) ||
                    CHECK_VALUE('DEBIT', :OLD.DEBIT, :NEW.DEBIT) ||
                    CHECK_VALUE('CREDIT', :OLD.CREDIT, :NEW.CREDIT) ||
                    CHECK_VALUE('NARRATION', :OLD.NARRATION, :NEW.NARRATION) ||
                    CHECK_VALUE('QUANTITY', :OLD.QUANTITY, :NEW.QUANTITY) ||
                    CHECK_VALUE('RATE', :OLD.RATE, :NEW.RATE) ||
                    CHECK_VALUE('ITEM CODE', :OLD.ITEM_CODE, :NEW.ITEM_CODE) ||
                    CHECK_VALUE('VAT_RATE', :OLD.VAT_RATE, :NEW.VAT_RATE) ||
                    CHECK_VALUE('DEPT', :OLD.DEPT, :NEW.DEPT) ||
                    CHECK_VALUE('ADMISSION NO',
                                :OLD.ADMISSION_NO,
                                :NEW.ADMISSION_NO) ||
                    CHECK_VALUE('STATUS', :OLD.STATUS, :NEW.STATUS) ||
                    CHECK_VALUE('UNIT', :OLD.UNIT, :NEW.UNIT) ||
                    CHECK_VALUE('BILL_NO', :OLD.BILL_NO, :NEW.BILL_NO) ||
                    CHECK_VALUE('BILL_DATE', :OLD.BILL_DATE, :NEW.BILL_DATE) ||
                    CHECK_VALUE('TR_MODE', :OLD.TR_MODE, :NEW.TR_MODE) ||
                    CHECK_VALUE('TR_NAME', :OLD.TR_NAME, :NEW.TR_NAME) ||
                    CHECK_VALUE('TR_NO', :OLD.TR_NO, :NEW.TR_NO) ||
                    CHECK_VALUE('TR_VALIDITY',
                                :OLD.TR_VALIDITY,
                                :NEW.TR_VALIDITY);
    insert into hisaudit
    values
      (USER,
       'MR',
       'MONEY RECEIPT HEADING INFORMATION',
       SYSDATE,
       'U',
       :old.refno,
       changedvalue);
  else
    oldvalue := 'DATE=' || :OLD.JDATE || '; ' || 'REF NO=' || :OLD.REFNO || '; ' ||
                'PATIENT ID=' || :OLD.PATIENT_ID || '; ' ||
                'ADMISSION DATE=' || :OLD.ADMISSION_DATE || '; ' ||
                'ACCOUNT CODE=' || :OLD.ACCOUNT_CODE || '; ' || 'DEBIT=' ||
                :OLD.DEBIT || '; ' || 'CREDIT=' || :OLD.CREDIT || '; ' ||
                'NARRATION=' || :OLD.NARRATION || '; ' || 'QUANTITY=' ||
                :OLD.QUANTITY || '; ' || 'RATE=' || :OLD.RATE || '; ' ||
                'ITEM CODE=' || :OLD.ITEM_CODE || '; ' || 'VAT RATE=' ||
                :OLD.VAT_RATE || '; ' || 'DEPT=' || :OLD.DEPT || '; ' ||
                'ADMISSION NO=' || :OLD.ADMISSION_NO || '; ' || 'STATUS=' ||
                :OLD.STATUS || '; ' || 'UNIT=' || :OLD.UNIT || '; ' ||
                'BILL NO=' || :OLD.BILL_NO || '; ' || 'BILL DATE=' ||
                :OLD.BILL_DATE || '; ' || 'TR MODE=' || :OLD.TR_MODE || '; ' ||
                'TR NAME=' || :OLD.TR_NAME || '; ' || 'TR NO=' ||
                :OLD.NARRATION || '; ' || 'TR VALIDITY=' ||
                :OLD.TR_VALIDITY || '; ';
    insert into hisaudit
    values
      (USER,
       'MR',
       'MONEY RECEIPT HEADING INFORMATION',
       SYSDATE,
       'D',
       :old.refno,
       oldvalue);
  end if;
end;
/