USE [METX]
GO
/****** Object:  Trigger [mt].[DueDate_Extension]    Script Date: 6/10/2026 1:35:37 PM ******/
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
-- C2385 - Due Date 1 Extension File

-- C2342 - Due Date 2
-- C2361 - Due Date 2 Extension File

-- C2372 - Due Date 3
-- C2397 - Due Date 3 Extension File

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

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;
		DECLARE @2365 CHAR(64);
		DECLARE @2385 CHAR(64);
		DECLARE @2361 CHAR(64);
		DECLARE @2397 CHAR(64);
--  Due Dates
		SELECT @2303 = C2303 FROM deleted;
		SELECT @2373 = C2373 FROM deleted;
		SELECT @2342 = C2342 FROM deleted;
		SELECT @2372 = C2372 FROM deleted;
--  File Names   
	    SELECT @2365 = C2365 FROM deleted;
		SELECT @2385 = C2385 FROM deleted;
		SELECT @2361 = C2381 FROM deleted;
		SELECT @2397 = C2397 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, C2365 = @2365 --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.C2385 = d.C2365, cal.C2342 = d.C2373, cal.C2361 = d.C2385,
		    cal.C2372 = d.C2342, cal.C2397 = d.C2361 -- 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;