Using Oracle Trigger Syntax

This example shows the Oracle trigger syntax.

/* Title: GET_PS_OPRID                                     */
/* Purpose: Retrieves the operator id (OPRID)              */
/*          from a VARCHAR2 comma separated field          */
/*          of the format 'OPRID,OS_USER,MACHINE'          */
/*          If no OPRID is found, it returns '!NoOPRID'    */
/* Limitations: (any grants, privileges, etc)              */
                                      */
drop function GET_PS_OPRID
/
create function GET_PS_OPRID (v_client_info VARCHAR2 )
        return VARCHAR2 is
        i integer;
begin
        if ( length(v_client_info) IS NULL ) then
                return('!NoOPRID');
        end if;
        if ( substr(v_client_info,1,1) = ',' ) then
                return('!NoOPRID');
        end if;
        i := 1;
        while ( (substr(v_client_info,i,1)) <> ',' and i < 32) loop
                i := i + 1;
        end loop;
        if ( i > 31 ) then
                return('!NoOPRID');
        else
                i := i - 1;
                return (substr (v_client_info, 1, i));
        end if;
end GET_PS_OPRID;
/
grant execute on GET_PS_OPRID to public
/
/* If Transaction is an Insert Or Update               */
/*    Capture After Values                             */
/* If Transaction is a Delete or Update                */
/*    Capture Before Values                            */
CREATE OR REPLACE TRIGGER PS_ABSENCE_HIST_TR
AFTER INSERT OR UPDATE OR DELETE ON PS_ABSENCE_HIST
FOR EACH ROW
DECLARE
        V_AUDIT_OPRID VARCHAR2(64);
BEGIN
    DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);
  IF :OLD.EMPLID IS NULL
  THEN
        INSERT INTO PS_AUDIT_ABSENCE
        VALUES (
                GET_PS_OPRID(V_AUDIT_OPRID) ,
                SYSDATE,
                'A',
                :NEW.EMPLID,
                :NEW.ABSENCE_TYPE,
                :NEW.BEGIN_DT,
                :NEW.RETURN_DT,
                :NEW.DURATION_DAYS,
                :NEW.DURATION_HOURS,
                :NEW.REASON,
                :NEW.PAID_UNPAID,
                :NEW.EMPLOYER_APPROVED
                );
  ELSE
      IF :NEW.EMPLID IS NULL
      THEN
        INSERT INTO PS_AUDIT_ABSENCE
        VALUES (
                GET_PS_OPRID(V_AUDIT_OPRID) ,
                SYSDATE,
                'D',
                :OLD.EMPLID,
                :OLD.ABSENCE_TYPE,
                :OLD.BEGIN_DT,
                :OLD.RETURN_DT,
                :OLD.DURATION_DAYS,
                :OLD.DURATION_HOURS,
                :OLD.REASON,
                :OLD.PAID_UNPAID,
                :OLD.EMPLOYER_APPROVED
                );
      ELSE
        INSERT INTO PS_AUDIT_ABSENCE
        VALUES (
                GET_PS_OPRID(V_AUDIT_OPRID) ,
                SYSDATE,
                'K',
                :OLD.EMPLID,
                :OLD.ABSENCE_TYPE,
                :OLD.BEGIN_DT,
                :OLD.RETURN_DT,
                :OLD.DURATION_DAYS,
                :OLD.DURATION_HOURS,
                :OLD.REASON,
                :OLD.PAID_UNPAID,
                :OLD.EMPLOYER_APPROVED
                );
        INSERT INTO PS_AUDIT_ABSENCE
        VALUES (
                GET_PS_OPRID(V_AUDIT_OPRID) ,
                SYSDATE ,
                'N',
                :NEW.EMPLID,
                :NEW.ABSENCE_TYPE,
                :NEW.BEGIN_DT,
                :NEW.RETURN_DT,
                :NEW.DURATION_DAYS,
                :NEW.DURATION_HOURS,
                :NEW.REASON,
                :NEW.PAID_UNPAID,
                :NEW.EMPLOYER_APPROVED
                );
      END IF;
  END IF;
END PS_ABSENCE_HIST_TR;
/