USE [METX]
GO
/****** Object:  Trigger [mt].[cal_d_after]    Script Date: 9/24/2024 11:34:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [mt].[cal_d_after]
	ON [mt].[Calibration]
	AFTER DELETE
/******************************************************************************************************************************************************/
/***	TSB	- 05/26/2016	No original conversion date, but modifications start here.																***/   
/***	If the trigger exists drop and create it.  Added transactions, uncommented the THROW statement in the catch block.							***/
/***	JCF - 06/09/2016	Rewrote triggers as data set processes																					***/
/***	JCF - 10/29/2017	added return when @@ROWCOUNT = 0 to stop trigger as soon as possible													***/
/******************************************************************************************************************************************************/
AS 
BEGIN
IF @@ROWCOUNT = 0 RETURN;
	BEGIN TRY
		SET NOCOUNT ON;
		BEGIN TRANSACTION;

		--Get equipment where current record was deleted.
		SELECT MTAG INTO #wat FROM deleted WHERE C2339 = 1;

		UPDATE mt.Calibration SET C2339 = 0 WHERE MTAG in (SELECT DISTINCT MTAG FROM #wat);
		UPDATE mt.Calibration SET C2339 = 1 WHERE CTAG in 
			(SELECT CTAG FROM mt.Calibration cal 
					JOIN (SELECT MTAG, 
						CAST(MAX(DATEADD(DAY, DATEDIFF(DAY, CAST('1900-1-1' AS DATE), C2301), CAST(convert(TIME, C2333) AS DATETIME2))) AS DATE) as C2301,  
						CAST(MAX(DATEADD(DAY, DATEDIFF(DAY, CAST('1900-1-1' AS DATE), C2301), CAST(convert(TIME, C2333) AS DATETIME2))) AS TIME) as C2333 
						from mt.Calibration where MTAG in (SELECT DISTINCT MTAG FROM inserted) group by MTAG) tmp 
					on (cal.MTAG=tmp.MTAG and cal.C2301 = tmp.C2301 and cal.C2333= CONVERT(NVARCHAR(8),tmp.C2333,108)))

		COMMIT TRANSACTION;
		SET NOCOUNT OFF;
	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
       THROW;
	END CATCH;
END

