CREATE OR REPLACE TRIGGER PSOPRDEFN_TR
AFTER INSERT OR UPDATE OR DELETE ON PSOPRDEFN
FOR EACH ROW
DECLARE
    V_AUDIT_OPRID VARCHAR2(64);
BEGIN

/*
    PSOPRDEFN_TR
    Developed by Praj Basnet - April 2009
    PeopleSoft Wiki: http://peoplesoft.wikidot.com/

    This version of the trigger has conditional logic to prevent an audit row
    being created for signon where no data in PSOPRDEFN actually changes but an
    update is performed. The delivered PSOPRDEFN_TR script generated through the
    update database level auditing functionality does not take this into account.

    Remember to replace PS_AUDIT_USERS with the appropriate audit table you are
    using. Also check that the fields in your audit table match those in this
    script. If not add/remove accordingly.
*/

DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);

IF INSERTING THEN
    INSERT INTO PS_AUDIT_USERS VALUES (
        GET_PS_OPRID(V_AUDIT_OPRID),
        SYSDATE,
        'A',
        :NEW.OPRID,
        :NEW.VERSION,
        :NEW.OPRDEFNDESC,
        :NEW.EMPLID,
        :NEW.EMAILID,
        :NEW.OPRCLASS,
        :NEW.ROWSECCLASS,
        :NEW.OPERPSWD,
        :NEW.ENCRYPTED,
        :NEW.SYMBOLICID,
        :NEW.ACCTLOCK,
        :NEW.PRCSPRFLCLS,
        :NEW.DEFAULTNAVHP,
        :NEW.OPRTYPE,
        :NEW.USERIDALIAS
    );

ELSE

    IF DELETING THEN

        INSERT INTO PS_AUDIT_USERS VALUES (
            GET_PS_OPRID(V_AUDIT_OPRID),
            SYSDATE,
            'D',
            :OLD.OPRID,
            :OLD.VERSION,
            :OLD.OPRDEFNDESC,
            :OLD.EMPLID,
            :OLD.EMAILID,
            :OLD.OPRCLASS,
            :OLD.ROWSECCLASS,
            :OLD.OPERPSWD,
            :OLD.ENCRYPTED,
            :OLD.SYMBOLICID,
            :OLD.ACCTLOCK,
            :OLD.PRCSPRFLCLS,
            :OLD.DEFAULTNAVHP,
            :OLD.OPRTYPE,
            :OLD.USERIDALIAS
        );

    ELSE

        IF  :NEW.OPRID != :OLD.OPRID OR
            :NEW.OPRDEFNDESC != :OLD.OPRDEFNDESC OR
            :NEW.EMPLID != :OLD.EMPLID OR
            :NEW.EMAILID != :OLD.EMAILID OR
            :NEW.OPRCLASS != :OLD.OPRCLASS OR
            :NEW.ROWSECCLASS != :OLD.ROWSECCLASS OR
            :NEW.OPERPSWD != :OLD.OPERPSWD OR
            :NEW.ENCRYPTED != :OLD.ENCRYPTED OR
            :NEW.SYMBOLICID != :OLD.SYMBOLICID OR
            :NEW.ACCTLOCK != :OLD.ACCTLOCK OR
            :NEW.PRCSPRFLCLS != :OLD.PRCSPRFLCLS OR
            :NEW.DEFAULTNAVHP != :OLD.DEFAULTNAVHP OR
            :NEW.OPRTYPE != :OLD.OPRTYPE OR
            :NEW.USERIDALIAS != :OLD.USERIDALIAS
            THEN

                INSERT INTO PS_AUDIT_USERS VALUES (
                    GET_PS_OPRID(V_AUDIT_OPRID),
                    SYSDATE,
                    'K',
                    :OLD.OPRID,
                    :OLD.VERSION,
                    :OLD.OPRDEFNDESC,
                    :OLD.EMPLID,
                    :OLD.EMAILID,
                    :OLD.OPRCLASS,
                    :OLD.ROWSECCLASS,
                    :OLD.OPERPSWD,
                    :OLD.ENCRYPTED,
                    :OLD.SYMBOLICID,
                    :OLD.ACCTLOCK,
                    :OLD.PRCSPRFLCLS,
                    :OLD.DEFAULTNAVHP,
                    :OLD.OPRTYPE,
                    :OLD.USERIDALIAS
                );

                INSERT INTO PS_AUDIT_USERS VALUES (
                    GET_PS_OPRID(V_AUDIT_OPRID),
                    SYSDATE,
                    'N',
                    :NEW.OPRID,
                    :NEW.VERSION,
                    :NEW.OPRDEFNDESC,
                    :NEW.EMPLID,
                    :NEW.EMAILID,
                    :NEW.OPRCLASS,
                    :NEW.ROWSECCLASS,
                    :NEW.OPERPSWD,
                    :NEW.ENCRYPTED,
                    :NEW.SYMBOLICID,
                    :NEW.ACCTLOCK,
                    :NEW.PRCSPRFLCLS,
                    :NEW.DEFAULTNAVHP,
                    :NEW.OPRTYPE,
                    :NEW.USERIDALIAS
                );

        END IF;

    END IF;

END IF;

END PSOPRDEFN_TR;
/