Loading ...
Sorry, an error occurred while loading the content.

Issue with Trigger

Expand Messages
  • raj
    we are creating trigger on PS_CUST_CONVER_HDR.we have created audit record PS_AUDIT_CUSTCNV_H for it. It contains the fiollowing fields. AUDIT_OPRID,
    Message 1 of 1 , Nov 22, 2010
    • 0 Attachment
      we are creating trigger on PS_CUST_CONVER_HDR.we have created audit record PS_AUDIT_CUSTCNV_H for it.

      It contains the fiollowing fields.
      AUDIT_OPRID,
      AUDIT_STAMP,
      AUDIT_ACTN,
      SETID,
      CUST_ID,
      CONVER_DTTM_INIT,
      CONVER_DT,
      CONVER_STATUS,
      LETTER_CD,
      FOLLOW_UP_ACTION,
      FOLLOW_UP_DT,
      PROMISE_DT,
      PROMISE_AMT

      Issue is Even if we change any other fields, other than these fields the row get inserted into the Audit record. Below is the generated sql from PIA. Please guide us on the same.

      Environment: PSFT FIN 8.9 , Tools 8.48 , DB is SQLServer





      CREATE TRIGGER CUST_CONVER_HDR_TR ON PS_CUST_CONVER_HDR
      FOR INSERT, UPDATE
      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 'Native SQL'
      else
      substring(cast(context_info as
      CHAR(128)),1,(charindex(',',cast(context_info as char(128)))-1))
      end
      FROM master..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_CUSTCNV_H
      (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,
      SETID,
      CUST_ID,
      CONVER_DTTM_INIT,
      CONVER_DT,
      CONVER_STATUS,
      LETTER_CD,
      FOLLOW_UP_ACTION,
      FOLLOW_UP_DT,
      PROMISE_DT,
      PROMISE_AMT)
      SELECT @OPRID,getdate(),'D',
      SETID,
      CUST_ID,
      CONVER_DTTM_INIT,
      CONVER_DT,
      CONVER_STATUS,
      LETTER_CD,
      FOLLOW_UP_ACTION,
      FOLLOW_UP_DT,
      PROMISE_DT,
      PROMISE_AMT FROM deleted
      END
      -- Transaction is a Insert
      IF (@XTYPE = 'I')
      BEGIN
      INSERT INTO PS_AUDIT_CUSTCNV_H
      (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,
      SETID,
      CUST_ID,
      CONVER_DTTM_INIT,
      CONVER_DT,
      CONVER_STATUS,
      LETTER_CD,
      FOLLOW_UP_ACTION,
      FOLLOW_UP_DT,
      PROMISE_DT,
      PROMISE_AMT)
      SELECT @OPRID,getdate(),'A',
      SETID,
      CUST_ID,
      CONVER_DTTM_INIT,
      CONVER_DT,
      CONVER_STATUS,
      LETTER_CD,
      FOLLOW_UP_ACTION,
      FOLLOW_UP_DT,
      PROMISE_DT,
      PROMISE_AMT FROM inserted
      END
      -- Transaction is a Update
      IF (@XTYPE = 'U')
      BEGIN
      -- Before Update
      INSERT INTO PS_AUDIT_CUSTCNV_H
      (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,
      SETID,
      CUST_ID,
      CONVER_DTTM_INIT,
      CONVER_DT,
      CONVER_STATUS,
      LETTER_CD,
      FOLLOW_UP_ACTION,
      FOLLOW_UP_DT,
      PROMISE_DT,
      PROMISE_AMT)
      SELECT @OPRID,getdate(),'K',
      SETID,
      CUST_ID,
      CONVER_DTTM_INIT,
      CONVER_DT,
      CONVER_STATUS,
      LETTER_CD,
      FOLLOW_UP_ACTION,
      FOLLOW_UP_DT,
      PROMISE_DT,
      PROMISE_AMT FROM deleted
      -- After Update
      INSERT INTO PS_AUDIT_CUSTCNV_H
      (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,
      SETID,
      CUST_ID,
      CONVER_DTTM_INIT,
      CONVER_DT,
      CONVER_STATUS,
      LETTER_CD,
      FOLLOW_UP_ACTION,
      FOLLOW_UP_DT,
      PROMISE_DT,
      PROMISE_AMT)
      SELECT @OPRID,getdate(),'N',
      SETID,
      CUST_ID,
      CONVER_DTTM_INIT,
      CONVER_DT,
      CONVER_STATUS,
      LETTER_CD,
      FOLLOW_UP_ACTION,
      FOLLOW_UP_DT,
      PROMISE_DT,
      PROMISE_AMT FROM inserted
      END
      go

      Thanks,
      Anand
    Your message has been successfully submitted and would be delivered to recipients shortly.