Dynamics GP - SQL Trigger - Track Customer Class, Item Type, Item Price Changes

--Create Changes log table


CREATE TABLE [dbo].[BI_ChangeTracking](

[Timestamp] [datetime] NULL,

[TableChanged] [nvarchar](50) NULL,

[FieldChanged] [nvarchar](50) NULL,

[FromValue] [nvarchar](50) NULL,

[ToValue] [nvarchar](50) NULL,

[UserChanged] [nvarchar](50) NULL,

[RecordID] [nvarchar](50) NULL,

[Description] [nvarchar](250) NULL

) ON [PRIMARY]




GO
 
 
--Create Trigger on customer table
  

CREATE TRIGGER [dbo].[after_update_class]

ON [dbo].[RM00101]

AFTER UPDATE

AS




BEGIN

Insert into BI_ChangeTracking(

[Timestamp], TableChanged, FieldChanged,

fromvalue,tovalue,

userchanged,recordid,[Description]

)

Select

getdate(),'RM00101','CUSTCLAS',

d.CUSTCLAS, i.CUSTCLAS,

USER_NAME(USER_ID()),i.CUSTNMBR,'Customer Class Changed'

from Inserted i inner join deleted d on (i.CUSTNMBR = d.CUSTNMBR)

Where (i.custclas <> '' or d.custclas <> '') and i.custclas <> d.custclas




END
  



GO
  

-- Create Trigger on item table
CREATE TRIGGER [dbo].[after_update_status]

ON [dbo].[IV00101]

AFTER UPDATE

AS




BEGIN
 
 


Insert into BI_ChangeTracking(

[Timestamp], TableChanged, FieldChanged,

fromvalue,tovalue,

userchanged,recordid,[Description]

)

Select

getdate(),'IV00101','ITEMTYPE',

d.ITEMTYPE, i.ITEMTYPE,

USER_NAME(USER_ID()),i.ITEMNMBR,'Item Type Changed 1-sales inv,2-discontinued, 3-kit, 4-misc charges, 5-services, 6-flatfee ,'

from Inserted i inner join deleted d on (i.ITEMNMBR = d.ITEMNMBR)

Where (i.ITEMTYPE <> '' or d.ITEMTYPE <> '') and i.ITEMTYPE <> d.ITEMTYPE




END
GO

--Create trigger on item price table

　

CREATE TRIGGER [dbo].[after_update_price]

ON [dbo].[IV00108]

AFTER UPDATE

AS




BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

Insert into BI_ChangeTracking(

[Timestamp], TableChanged, FieldChanged,

fromvalue,tovalue,

userchanged,recordid,[Description]

)

Select

getdate(),'IV00108','UOMPRICE',

d.UOMPRICE, i.UOMPRICE,

USER_NAME(USER_ID()),rtrim(i.ITEMNMBR) + ' | ' + i.PRCLEVEL,'Item Price Changed'

from Inserted i inner join deleted d on (i.ITEMNMBR = d.ITEMNMBR and i.PRCLEVEL = d.PRCLEVEL)

Where (i.UOMPRICE <> 0 or d.UOMPRICE <> 0) and i.UOMPRICE <> d.UOMPRICE




END
  




  




　
 GO