PeopleSoft Audit Trigger

最新推荐文章于 2019-08-21 22:39:21 发布

WTK870424

最新推荐文章于 2019-08-21 22:39:21 发布

阅读量825

点赞数

分类专栏： peoplesoft 文章标签： insert user each os

本文链接：https://blog.csdn.net/WTK870424/article/details/6412143

版权

peoplesoft 专栏收录该内容

203 篇文章 10 订阅

订阅专栏

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;