Dynamics GP - Trigger to track if VAT is changed on an SOP transaction

--USE COMPANY DATABASE

CREATE TABLE [dbo].[BI_SOP10105_Tracking](
[SOPTYPE] [smallint] NOT NULL,
[SOPNUMBE] [char](21) NOT NULL,
[LNITMSEQ] [int] NOT NULL,
[TAXDTLID] [char](15) NOT NULL,
[ChangeType] [char](50) NOT NULL,
[ChangeDateTime] [datetime] NOT NULL,
[OLDSTAXAMNT] [numeric](19, 5) NOT NULL,
[NEWSTAXAMNT] [numeric](19, 5) NOT NULL,
[USERID] [char](50) NOT NULL,
[RowID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_BI_SOP10105_Tracking] PRIMARY KEY CLUSTERED
(
[SOPTYPE] ASC,
[SOPNUMBE] ASC,
[LNITMSEQ] ASC,
[TAXDTLID] ASC,
[ChangeDateTime] ASC,
[RowID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

GRANT DELETE, INSERT, SELECT, UPDATE ON [dbo].[BI_SOP10105_Tracking] TO [DYNGRP]
GO

--USE COMPANY DATABASE

IF EXISTS (SELECT * FROM   sysobjects
   WHERE  id = object_id(N'[dbo].[BI_SOP10105_D]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
   DROP TRIGGER [dbo].[BI_SOP10105_D]
GO

CREATE TRIGGER BI_SOP10105_D ON dbo.SOP10105
FOR DELETE
AS

BEGIN TRY
INSERT INTO BI_SOP10105_Tracking
SELECT
SOPTYPE,
SOPNUMBE,
LNITMSEQ,
TAXDTLID,
'Delete',
GETDATE(),
STAXAMNT,
0,
USER_NAME()
FROM deleted
END TRY

BEGIN CATCH
-- exit
END CATCH
GO

--USE COMPANY DATABASE

IF EXISTS (SELECT * FROM   sysobjects
   WHERE  id = object_id(N'[dbo].[BI_SOP10105_I_U]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
   DROP TRIGGER [dbo].[BI_SOP10105_I_U]
GO

CREATE TRIGGER BI_SOP10105_I_U ON dbo.SOP10105
FOR INSERT, UPDATE
AS

BEGIN TRY
INSERT INTO BI_SOP10105_Tracking
SELECT
i.SOPTYPE,
i.SOPNUMBE,
i.LNITMSEQ,
i.TAXDTLID,
CASE WHEN d.SOPTYPE IS NULL THEN 'Insert' ELSE 'Update' END,
GETDATE(),
CASE WHEN d.SOPTYPE IS NULL THEN 0 ELSE d.STAXAMNT END,
i.STAXAMNT,
USER_NAME()
FROM inserted i
LEFT OUTER JOIN deleted d ON i.SOPTYPE = d.SOPTYPE AND i.SOPNUMBE = d.SOPNUMBE AND i.LNITMSEQ = d.LNITMSEQ AND i.TAXDTLID = d.TAXDTLID
WHERE d.SOPTYPE IS NULL
OR (NOT d.SOPTYPE IS NULL AND d.STAXAMNT <> i.STAXAMNT)
END TRY

BEGIN CATCH
-- exit
END CATCH

GO

----------------------------------------------------------------------------------
View to display changes
-----------------------------------------------------------------------------------
/****** Object:  View [dbo].[BI_AUDIT_SOP10105_1]    Script Date: 9/2/2019 7:15:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_AUDIT_SOP10105_1]
AS
SELECT     TOP (100) PERCENT SOPNUMBE, OLDSTAXAMNT AS OldTaxAmt, NEWSTAXAMNT AS NewTaxAmt, LNITMSEQ, MIN(RowID) AS RowID, USERID, MAX(OLDSTAXAMNT) AS OrigOldTaxAmt, COUNT(RowID) AS Count
FROM        dbo.DAV_SOP10105_Tracking
GROUP BY SOPNUMBE, LNITMSEQ, USERID, OLDSTAXAMNT, NEWSTAXAMNT
HAVING     (LNITMSEQ > 0)
ORDER BY RowID

GO


/****** Object:  View [dbo].[BI_AUDIT_SOP10105_2]    Script Date: 9/2/2019 7:15:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_AUDIT_SOP10105_2]
AS
SELECT     dbo.BI_AUDIT_SOP10105_1.SOPNUMBE, MAX(dbo.BI_AUDIT_SOP10105_1.OrigOldTaxAmt) AS OrigOldTaxAmt, SUM(dbo.BI_AUDIT_SOP10105_1.OldTaxAmt) AS OldTaxAmt, SUM(dbo.BI_AUDIT_SOP10105_1.NewTaxAmt) AS NewTaxAmt,
                  dbo.BI_AUDIT_SOP10105_1.LNITMSEQ / 16384 AS LNITMSEQ, MAX(dbo.BI_AUDIT_SOP10105_1.RowID) AS RowID, dbo.BI_AUDIT_SOP10105_1.USERID, allsop.SOPTYPE, allsop.DOCID, allsop.CUSTNMBR, allsop.CUSTNAME
FROM        dbo.BI_AUDIT_SOP10105_1 LEFT OUTER JOIN
                      (SELECT     SOPTYPE, SOPNUMBE, DOCID, DOCDATE, CUSTNMBR, CUSTNAME
                       FROM        dbo.SOP10100
                       UNION
                       SELECT     SOPTYPE, SOPNUMBE, DOCID, DOCDATE, CUSTNMBR, CUSTNAME
                       FROM        dbo.SOP30200) AS allsop ON dbo.BI_AUDIT_SOP10105_1.SOPNUMBE = allsop.SOPNUMBE
GROUP BY dbo.BI_AUDIT_SOP10105_1.SOPNUMBE, dbo.BI_AUDIT_SOP10105_1.LNITMSEQ / 16384, dbo.BI_AUDIT_SOP10105_1.USERID, allsop.SOPTYPE, allsop.DOCID, allsop.CUSTNMBR, allsop.CUSTNAME

GO

/****** Object:  View [dbo].[BI_AUDIT_SOP10105_3]    Script Date: 9/2/2019 7:15:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_AUDIT_SOP10105_3]
AS
SELECT        SOPNUMBE, OrigOldTaxAmt, CASE WHEN LEFT(sopnumbe, 2) = 'OM' THEN NewTaxamt ELSE NewTaxAmt - OldTaxAmt END AS NewTaxAmt,
                         CASE WHEN newtaxamt <> oldtaxamt THEN 'Tax Changed' ELSE '' END AS [Tax Changed], LNITMSEQ, RowID, USERID, SOPTYPE, DOCID, CUSTNMBR, CUSTNAME, OldTaxAmt
FROM            dbo.BI_AUDIT_SOP10105_2

GO