/*
    PSOPRDEFN_TR for Microsoft SQL Server
    Developed by Praj Basnet - August 2010
    PeopleSoft Wiki: http://www.peoplesoftwiki.com/
 
	  This version of the trigger records the username and machine from which a
		database user is logged in from, if they make a database level change to
		PSOPRDEFN, as the AUDIT_OPRID.
 
    It also 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.
*/


CREATE TRIGGER PSOPRDEFN_TR ON PSOPRDEFN
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
DECLARE @XTYPE CHAR(1), @OPRID CHAR(30)
SET @OPRID = NULL
SELECT @OPRID = case (charindex(',',
cast(context_info as char(128))))
when 0 then (select SYSTEM_USER + '@' + HOST_NAME())
else 
substring(cast(context_info as
CHAR(128)),1,(charindex(',',cast(context_info as char(128)))-1))
end
FROM sys.sysprocesses
WHERE spid = @@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 PS_AUDIT_USERS
(AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,
OPRID,
VERSION,
OPRDEFNDESC,
EMPLID,
EMAILID,
OPRCLASS,
ROWSECCLASS,
OPERPSWD,
ENCRYPTED,
SYMBOLICID,
ACCTLOCK,
PRCSPRFLCLS,
DEFAULTNAVHP,
OPRTYPE,
USERIDALIAS)
SELECT @OPRID,getdate(),'D',
OPRID,
VERSION,
OPRDEFNDESC,
EMPLID,
EMAILID,
OPRCLASS,
ROWSECCLASS,
OPERPSWD,
ENCRYPTED,
SYMBOLICID,
ACCTLOCK,
PRCSPRFLCLS,
DEFAULTNAVHP,
OPRTYPE,
USERIDALIAS FROM deleted 
END
-- Transaction is a Insert
IF (@XTYPE = 'I')
BEGIN
INSERT INTO PS_AUDIT_USERS
(AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,
OPRID,
VERSION,
OPRDEFNDESC,
EMPLID,
EMAILID,
OPRCLASS,
ROWSECCLASS,
OPERPSWD,
ENCRYPTED,
SYMBOLICID,
ACCTLOCK,
PRCSPRFLCLS,
DEFAULTNAVHP,
OPRTYPE,
USERIDALIAS)
SELECT @OPRID,getdate(),'A',
OPRID,
VERSION,
OPRDEFNDESC,
EMPLID,
EMAILID,
OPRCLASS,
ROWSECCLASS,
OPERPSWD,
ENCRYPTED,
SYMBOLICID,
ACCTLOCK,
PRCSPRFLCLS,
DEFAULTNAVHP,
OPRTYPE,
USERIDALIAS FROM inserted 
END
-- Transaction is a Update
IF (@XTYPE = 'U')
BEGIN
-- Before Update
INSERT INTO PS_AUDIT_USERS
(AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,
OPRID,
VERSION,
OPRDEFNDESC,
EMPLID,
EMAILID,
OPRCLASS,
ROWSECCLASS,
OPERPSWD,
ENCRYPTED,
SYMBOLICID,
ACCTLOCK,
PRCSPRFLCLS,
DEFAULTNAVHP,
OPRTYPE,
USERIDALIAS)
SELECT @OPRID,getdate(),'K',
d.OPRID,
d.VERSION,
d.OPRDEFNDESC,
d.EMPLID,
d.EMAILID,
d.OPRCLASS,
d.ROWSECCLASS,
d.OPERPSWD,
d.ENCRYPTED,
d.SYMBOLICID,
d.ACCTLOCK,
d.PRCSPRFLCLS,
d.DEFAULTNAVHP,
d.OPRTYPE,
d.USERIDALIAS 
FROM 
	deleted d INNER JOIN PSOPRDEFN o
	ON d.OPRID = o.OPRID
WHERE
	d.VERSION != o.VERSION or
  d.OPRDEFNDESC != o.OPRDEFNDESC or
	d.EMPLID != o.EMPLID or
	d.EMAILID != o.EMAILID or
	d.OPRCLASS != o.OPRCLASS or
	d.ROWSECCLASS != o.ROWSECCLASS or
	d.OPERPSWD != o.OPERPSWD or
	d.ENCRYPTED != o.ENCRYPTED or
	d.SYMBOLICID != o.SYMBOLICID or
	d.ACCTLOCK != o.ACCTLOCK or
	d.PRCSPRFLCLS != o.PRCSPRFLCLS or
	d.DEFAULTNAVHP != o.DEFAULTNAVHP or
	d.OPRTYPE != o.OPRTYPE or
	d.USERIDALIAS != o.USERIDALIAS

-- After Update
INSERT INTO PS_AUDIT_USERS
(AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,
OPRID,
VERSION,
OPRDEFNDESC,
EMPLID,
EMAILID,
OPRCLASS,
ROWSECCLASS,
OPERPSWD,
ENCRYPTED,
SYMBOLICID,
ACCTLOCK,
PRCSPRFLCLS,
DEFAULTNAVHP,
OPRTYPE,
USERIDALIAS)
SELECT @OPRID,getdate(),'N',
i.OPRID,
i.VERSION,
i.OPRDEFNDESC,
i.EMPLID,
i.EMAILID,
i.OPRCLASS,
i.ROWSECCLASS,
i.OPERPSWD,
i.ENCRYPTED,
i.SYMBOLICID,
i.ACCTLOCK,
i.PRCSPRFLCLS,
i.DEFAULTNAVHP,
i.OPRTYPE,
i.USERIDALIAS 
FROM inserted i INNER JOIN PSOPRDEFN o
	ON i.OPRID = o.OPRID
WHERE
  i.VERSION != o.VERSION or
  i.OPRDEFNDESC != o.OPRDEFNDESC or
	i.EMPLID != o.EMPLID or
	i.EMAILID != o.EMAILID or
	i.OPRCLASS != o.OPRCLASS or
	i.ROWSECCLASS != o.ROWSECCLASS or
	i.OPERPSWD != o.OPERPSWD or
	i.ENCRYPTED != o.ENCRYPTED or
	i.SYMBOLICID != o.SYMBOLICID or
	i.ACCTLOCK != o.ACCTLOCK or
	i.PRCSPRFLCLS != o.PRCSPRFLCLS or
	i.DEFAULTNAVHP != o.DEFAULTNAVHP or
	i.OPRTYPE != o.OPRTYPE or
	i.USERIDALIAS != o.USERIDALIAS
END
go