How To: Audit PeopleSoft Records
Background:
PeopleSoft delivers audit functionality but it is not comprehensive. For example if an update comes from outside of PeopleSoft (from an interface or from a SQL update) it only logs before/after information but does not show us the login information. Delivered functionality also logs superfluous updates (ie: if you update a column to “1” but the value was already “1” it still triggers the audit). Due to this we have developed a standard for auditing outside of App Designer. The information and example code below can be used for new audit requests.
Notes:
- The audit table/index/trigger will be created outside of PeopleSoft by a script that should be attached to the RFC.
- If business users need to access the data using PSQUERY then a view can be built against the audit table (and that view can subsequently be used in PSQUERY)
- Audit tables/triggers should not be created against large transactional tables as that can have an impact on performance.
- The clustered index keys should always be in the list of audited columns. If they are not the code to prevent logging superfluous updates may not work in certain rare border conditions.
Naming conventions:
Audit table: AUDIT_<PS_TABLE_NAME>
Audit table index: AUDIT_<PS_TABLE_NAME>
Audit table trigger: AUDIT_<PS_TABLE_NAME>_TR
Indexes:
One index is created on all audit tables on the “AUDIT_OPRID” and “AUDIT_STAMP” fields as non-unique clustered indexes. It is important that the index is not unique since we are logging audit information and there can be duplicates based on these fields.
Example (auditing PS_DEPT_TBL on SETID,DEPTID,EFFDT,DESCR,EFF_STATUS fields):
-- ****************************************
-- ****************************************
-- *** PS_DEPT_TBL
-- *** Create audit table:
-- *** AUDIT_PS_DEPT_TBL
-- ****************************************
-- ****************************************
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'U' and name = 'AUDIT_PS_DEPT_TBL'
AND schema_id = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo'))
DROP TABLE [dbo].[AUDIT_PS_DEPT_TBL]
GO
CREATE TABLE [dbo].[AUDIT_PS_DEPT_TBL] (
AUDIT_OPRID CHAR(30) NOT NULL,
AUDIT_STAMP PSDATETIME NULL,
AUDIT_ACTN CHAR(1) NOT NULL,
AUDIT_HOST_NAME VARCHAR(256) NOT NULL,
AUDIT_PROGRAM_NAME VARCHAR(256) NOT NULL,
AUDIT_LOGIN_NAME VARCHAR(256) NOT NULL,
AUDIT_CONTEXT_INFO CHAR(128) NOT NULL,
SETID CHAR(5) NOT NULL,
DEPTID CHAR(10) NOT NULL,
EFFDT PSDATE NULL,
DESCR CHAR(30) NOT NULL,
EFF_STATUS CHAR(1) NOT NULL)
go
CREATE CLUSTERED INDEX [AUDIT_PS_DEPT_TBL] ON [dbo].[AUDIT_PS_DEPT_TBL]
(AUDIT_OPRID,
AUDIT_STAMP)
go
-- *******************************************
-- *** Create audit trigger for PS_DEPT_TBL:
-- *** AUDIT_PS_DEPT_TBL_TR
-- *******************************************
IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'TR' and name = 'AUDIT_PS_DEPT_TBL_TR'
AND schema_id = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo'))
EXEC ('CREATE TRIGGER [dbo].[AUDIT_PS_DEPT_TBL_TR] ON [PS_DEPT_TBL] FOR INSERT AS PRINT ''Stub version, to be replaced'' ')
GO
ALTER TRIGGER [dbo].[AUDIT_PS_DEPT_TBL_TR] ON [PS_DEPT_TBL]
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
DECLARE @XTYPE CHAR(1),
@OPRID CHAR(30),
@AUDIT_HOST_NAME VARCHAR(256),
@AUDIT_PROGRAM_NAME VARCHAR(256),
@AUDIT_LOGIN_NAME VARCHAR(256),
@AUDIT_CONTEXT_INFO CHAR(128)
SET @OPRID = NULL
SELECT @OPRID = case (charindex(',', cast(context_info as char(128))))
when 0 then 'Native SQL'
else
substring(cast(context_info as CHAR(128)),1,(charindex(',',cast(context_info as char(128)))-1))
end,
@AUDIT_HOST_NAME = host_name,
@AUDIT_PROGRAM_NAME = program_name,
@AUDIT_LOGIN_NAME = login_name,
@AUDIT_CONTEXT_INFO = cast(context_info as CHAR(128))
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
-- Determine Transaction Type
IF EXISTS (SELECT * FROM DELETED)
BEGIN
SET @XTYPE = 'D'
END
IF EXISTS (SELECT * FROM INSERTED)
BEGIN
IF (@XTYPE = 'D')
BEGIN
SET @XTYPE = 'U'
END
ELSE
BEGIN
SET @XTYPE = 'I'
END
END
-- Transaction is a Delete
IF (@XTYPE = 'D')
BEGIN
INSERT INTO [dbo].[AUDIT_PS_DEPT_TBL]
(AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,AUDIT_HOST_NAME,AUDIT_PROGRAM_NAME,AUDIT_LOGIN_NAME,AUDIT_CONTEXT_INFO,
SETID,
DEPTID,
EFFDT,
DESCR,
EFF_STATUS
)
SELECT @OPRID,getdate(),'D', @AUDIT_HOST_NAME,@AUDIT_PROGRAM_NAME,@AUDIT_LOGIN_NAME,@AUDIT_CONTEXT_INFO,
SETID,
DEPTID,
EFFDT,
DESCR,
EFF_STATUS FROM deleted
END
-- Transaction is a Insert
IF (@XTYPE = 'I')
BEGIN
INSERT INTO [dbo].[AUDIT_PS_DEPT_TBL]
(AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,AUDIT_HOST_NAME,AUDIT_PROGRAM_NAME,AUDIT_LOGIN_NAME,AUDIT_CONTEXT_INFO,
SETID,
DEPTID,
EFFDT,
DESCR,
EFF_STATUS)
SELECT @OPRID,getdate(),'A', @AUDIT_HOST_NAME,@AUDIT_PROGRAM_NAME,@AUDIT_LOGIN_NAME,@AUDIT_CONTEXT_INFO,
SETID,
DEPTID,
EFFDT,
DESCR,
EFF_STATUS FROM inserted
END
-- Transaction is a Update
IF (@XTYPE = 'U')
BEGIN
-- Only record the before/after values during an update if we find that
-- one or more of the audited columns of data have changed.
-- NOTE: the clustered index keys should be in the list of audited
-- columns. If they are not then this code may not work in certain (rare) border conditions.
IF EXISTS (SELECT SETID,
DEPTID,
EFFDT,
DESCR,
EFF_STATUS FROM INSERTED EXCEPT SELECT SETID,
DEPTID,
EFFDT,
DESCR,
EFF_STATUS FROM DELETED)
BEGIN
-- Before Update
INSERT INTO [dbo].[AUDIT_PS_DEPT_TBL]
(AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,AUDIT_HOST_NAME,AUDIT_PROGRAM_NAME,AUDIT_LOGIN_NAME,AUDIT_CONTEXT_INFO,
SETID,
DEPTID,
EFFDT,
DESCR,
EFF_STATUS)
SELECT @OPRID,getdate(),'K', @AUDIT_HOST_NAME,@AUDIT_PROGRAM_NAME,@AUDIT_LOGIN_NAME,@AUDIT_CONTEXT_INFO,
SETID,
DEPTID,
EFFDT,
DESCR,
EFF_STATUS FROM deleted
-- After Update
INSERT INTO [dbo].[AUDIT_PS_DEPT_TBL]
(AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,AUDIT_HOST_NAME,AUDIT_PROGRAM_NAME,AUDIT_LOGIN_NAME,AUDIT_CONTEXT_INFO,
SETID,
DEPTID,
EFFDT,
DESCR,
EFF_STATUS)
SELECT @OPRID,getdate(),'N', @AUDIT_HOST_NAME,@AUDIT_PROGRAM_NAME,@AUDIT_LOGIN_NAME,@AUDIT_CONTEXT_INFO,
SETID,
DEPTID,
EFFDT,
DESCR,
EFF_STATUS FROM inserted
END
END
