PeopleSoft Audit Trigger

The trigger below audits any changes to the PeopleSoft PSOPRDEFN table and writes the pertinent information to PS_NX_AUD_OPRDEFN.

8

CREATE OR REPLACE TRIGGER AUDIT_PSOPRDEFN
AFTER INSERT OR UPDATE ON SYSADM.PSOPRDEFN
FOR EACH ROW
DECLARE NXCONNECTINFO VARCHAR2(64); NXUSER VARCHAR2(30); NXOPRID  VARCHAR2(30); NXTIME  DATE;
BEGIN
SELECT 'user='||sys_context('userenv','os_user')||' ip='||sys_context('userenv','ip_address')||
' host='||sys_context('userenv','host') into NXCONNECTINFO from dual;
select sys_context('userenv','os_user') into NXUSER from DUAL;
 
IF NXUSER != 'psoft'
THEN
  NXOPRID := ' ';
  NXTIME  := NULL;
ELSE
  NXOPRID := :NEW.LASTUPDOPRID;
  NXTIME  := :NEW.LASTUPDDTTM;
END IF;
 
IF :NEW.OPERPSWD <> :OLD.OPERPSWD
THEN
  INSERT INTO SYSADM.PS_NX_AUD_OPRDEFN VALUES (USER, SYSDATE, :OLD.OPRID, 'Password Changed', NXCONNECTINFO, NXOPRID, NXTIME);
END IF;
 
IF :NEW.ACCTLOCK <> :OLD.ACCTLOCK
THEN
  IF :NEW.ACCTLOCK = 1
  THEN
    INSERT INTO SYSADM.PS_NX_AUD_OPRDEFN values (USER, SYSDATE, :OLD.OPRID, 'Account Locked', NXCONNECTINFO, NXOPRID, NXTIME);
  ELSE
    INSERT INTO SYSADM.PS_NX_AUD_OPRDEFN values (USER, SYSDATE, :OLD.OPRID, 'Account Unlocked', NXCONNECTINFO, NXOPRID, NXTIME);
  END IF;
END IF;
 
IF :NEW.LASTUPDOPRID <> :OLD.LASTUPDOPRID
THEN
  INSERT INTO SYSADM.PS_NX_AUD_OPRDEFN values (USER, SYSDATE, :OLD.OPRID, 'LASTUPDOPRID Changed', NXCONNECTINFO, NXOPRID, NXTIME);
END IF;
 
END;
/
 
ALTER TRIGGER AUDIT_PSOPRDEFN DISABLE;