USE [METX]
GO
/****** Object:  Trigger [mt].[DueDate_Extension]    Script Date: 5/19/2026 8:02:43 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:	Doug Lynde, Dan Fillpot - On Time Support, Inc.
-- Create date: May 17, 2026
-- Description:	This is a Due Date Extension Trigger to keep the previous due date within the same calibration record.
-- This Trigger will allow 3 Date Fields to keep track of Due Changes in the Calibration Record. 
-- Due Date Fields affected are C2373 - First Field, C2342 - Second Field, C2372 - Third Field

-- C2373 - Due Date 1

-- C2342 - Due Date 2

-- C2372 - Due Date 3

-- =============================================

ALTER TRIGGER [mt].[DueDate_Extension]
   ON  [mt].[Calibration]
  AFTER UPDATE

AS

BEGIN
	SET NOCOUNT ON;

-- Only run if the source column was part of the update
	IF UPDATE(C2303) --if changed
	BEGIN

		DECLARE @2303 DATE;
		DECLARE @2373 DATE;
		DECLARE @2342 DATE;
		DECLARE @2372 DATE;
		SELECT @2303 = C2303 FROM deleted;
		SELECT @2373 = C2373 FROM deleted;
		SELECT @2342 = C2342 FROM deleted;
		SELECT @2372 = C2372 FROM deleted;

		IF( @2373 IS NOT NULL AND @2342 IS NOT NULL AND @2372 IS NOT NULL ) --extensions are full
			BEGIN
				UPDATE cal SET C2303 = @2303 --reset C2303 to not allow further extensions
				FROM mt.calibration cal
				INNER JOIN deleted d ON cal.ctag = d.ctag
				RETURN --exit, no more extensions allowed
			END

		UPDATE cal
		SET cal.C2373 = d.C2303, cal.C2342 = d.C2373, cal.C2372 = d.C2342 -- Copies the ORIGINAL value from the deleted/updated table
		FROM mt.calibration cal
		INNER JOIN inserted i ON cal.ctag = i.ctag
		INNER JOIN deleted d ON cal.ctag = d.ctag
		WHERE d.C2303 IS NOT NULL -- Original value wasn't null
		AND (i.C2303 <> d.C2303) -- Value actually changed
	END
END;