USE TRUCK
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================================================================
-- Author:		David Morinello
-- Create date: 8/16/2023
-- V 0.5 - Tracks Customer Name, Customer Name, Credit Limit Amount, Payment Terms ID, & Primary BilltoAddress Code
--         to the Table RM00101_Audit_Tracking
-- Description:	Monitor for RM00101 changes to the RM00101_Audit_Tracking Change Audit Table  
-- Proof of Concept Testing with One Field
-- =============================================================================================

--New SQL 2016 command
CREATE OR ALTER TRIGGER [dbo].[tr_RM00101_ChangeTracking]
   ON  RM00101
   FOR UPDATE
AS 
--BEGIN
SET NOCOUNT ON;

   IF (UPDATE (CUSTNAME) OR UPDATE (CRLMTAMT) OR UPDATE (PYMTRMID) )  
    --IF (UPDATE(CUSTNAME))
    BEGIN
	-- Audit OLD record
		INSERT INTO RM00101_Audit_Tracking
		SELECT  del.CUSTNMBR     --CUSTNMBR
               ,del.CUSTNAME     --CUSTNAME
		       ,del.CRLMTAMT     --CRLMTAMT
		       ,del.PYMTRMID     --PYMTRMID
			   ,del.PRBTADCD     --PRBTADCD
			   	,'Original'      --ChangeNote
			   ,GETDATE()        --ChangeDate
			   ,ORIGINAL_LOGIN() --ChgUser
	    FROM DELETED AS del 
		    --FROM DELETED AS del INNER JOIN INSERTED AS ins
			   --ON del.CUSTNMBR = ins.CUSTNMBR AND del.ADRSCODE = ins.ADRSCODE
	END

	BEGIN
	-- Audit NEW record
		INSERT INTO RM00101_Audit_Tracking
		SELECT  ins.CUSTNMBR
		       ,ins.CUSTNAME
		       ,ins.CRLMTAMT
		       ,ins.PYMTRMID
			   ,ins.PRBTADCD
			   ,'Changed'
			   ,GETDATE()
			   ,ORIGINAL_LOGIN()
	    FROM INSERTED AS ins
	    --FROM DELETED AS del INNER JOIN INSERTED AS ins
			  -- ON del.CUSTNMBR = ins.CUSTNMBR --AND del.ADRSCODE = ins.ADRSCODE

    END

--END
GO

ALTER TABLE [dbo].[RM00101] ENABLE TRIGGER [tr_RM00101_ChangeTracking]
GO






/*
exec sp_settriggerorder @triggername = 'tr_RM00101_ChangeTracking', 
@order = 'last', 
@stmttype = 'UPDATE', 
@namespace = null 
GO

SELECT name,
  IsFirst = OBJECTPROPERTY(object_id, 'ExecIsFirstInsertTrigger'),
  IsLast  = OBJECTPROPERTY(object_id ,'ExecIsLastInsertTrigger')
FROM sys.triggers
WHERE parent_id = OBJECT_ID(N'dbo.RM00101');

*/

--ALTER TABLE [dbo].[RM00101] DISABLE TRIGGER [tr_RM00101_ChangeTracking]

--DROP Create TRIGGER tr_RM00101_ChangeTracking

/*
USE [TRUCK]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RM00101_Audit_Tracking](
	[CUSTNMBR] [CHAR](15) NOT NULL,
	[CUSTNAME] [CHAR](65) NULL,
	[CRLMTAMT] NUMERIC (19,5) NULL,
	[PYMTRMID] [CHAR](21) NULL,
	[PRBTADCD] [CHAR](15) NULL,
    [ChangeNote][CHAR](15) NOT NULL,
	[ChangeDate] [DATETIME] NULL,
	[ChgUser] [NVARCHAR](128) NOT NULL,
	[DEX_ROW_TS] [DATETIME] NOT NULL,
	[DEX_ROW_ID] [INT] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKRM00101_Change] PRIMARY KEY NONCLUSTERED 
(
	[CUSTNMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [P[PRIMARY]
GO

*/