USE [METX]
GO
/****** Object:  Trigger [mt].[cal_u_instead]    Script Date: 9/24/2024 11:44:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [mt].[cal_u_instead]
	ON [mt].[Calibration]
   INSTEAD OF UPDATE
/******************************************************************************************************************************************************/
/***	TSB	- 05/26/2016	No original conversion date, but modifications start here.																***/   
/***	Added transactions, uncommented the THROW statement in the catch block																		***/
/***	JCF - 06/09/2016	Rewrote triggers as data set processes																					***/
/***	JCF - 02/08/2017	Corrected the removal of domain from SYSTEM_USER for modified by field													***/
/***	JCF	- 03/03/2017	Modified by is now overwritten by server time																			***/
/***	AGH	- 06/07/2017	Updated 'modified by' (C2338) to accept value being passed from METx2 or use system user if no value is provided		***/
/***	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;

		SELECT * INTO #tmp_cal FROM inserted;
		
		SELECT i.CTAG as ins_ctag, i.C2301 as ins_2301, d.C2301 as del_2301, i.C2333 as ins_2333, d.C2333 as del_2333,
			i.C2308 as ins_2308, d.C2308 as del_2308, i.C2323 as ins_2323, d.C2323 as del_2323, 
			COALESCE(i.C2324, 0) as ins_2324, COALESCE(d.C2324, 0) as del_2324, 
			COALESCE(i.C2328, 0) as ins_2328, COALESCE(d.C2328, 0) as del_2328, 
			COALESCE(i.C2337,'1970-01-01') as ins_2337, COALESCE(d.C2337,'1970-01-01') as del_2337,
			COALESCE(i.C2338, N'') as ins_2338, COALESCE(d.C2338, N'') as del_2338 INTO #watevs
			FROM inserted i join deleted d on (i.CTAG = d.CTAG OR (i.mtag=d.mtag and i.C2301=d.C2301 and i.C2333=d.C2333))
			WHERE i. C2301 <> d.C2301 OR i.C2302 <> d.C2302 OR i.C2303 <> d.C2303 OR i.C2304 <> d.C2304 OR i.C2306 <> d.C2306 
				OR i.C2307 <> d.C2307 OR i.C2309 <> d.C2309 OR i.C2310 <> d.C2310 OR i.C2311 <> d.C2311 OR i.C2312 <> d.C2312 
				OR i.C2313 <> d.C2313 OR i.C2314 <> d.C2314 OR i.C2316 <> d.C2316 OR i.C2320 <> d.C2320 OR i.C2321 <> d.C2321 
				OR i.C2322 <> d.C2322 OR i.C2323 <> d.C2323 OR i.C2324 <> d.C2324 OR i.C2326 <> d.C2326 OR i.C2327 <> d.C2327 
				OR i.C2328 <> d.C2328 OR i.C2329 <> d.C2329 OR i.C2330 <> d.C2330 OR i.C2331 <> d.C2331 OR i.C2332 <> d.C2332 
				OR i.C2333 <> d.C2333 OR i.C2334 <> d.C2334 OR i.C2335 <> d.C2335 OR i.C2336 <> d.C2336 OR i.C2337 <> d.C2337 
				OR i.C2338 <> d.C2338 OR i.C2339 <> d.C2339 OR i.C2341 <> d.C2341 OR i.C2362 <> d.C2362 OR i.C2308 <> d.C2308 
				OR i.C2315 <> d.C2315 OR i.C2342 <> d.C2342 OR i.C2343 <> d.C2343 OR i.C2317 <> d.C2317 OR i.C2318 <> d.C2318 
				OR i.C2319 <> d.C2319 OR i.C2325 <> d.C2325 OR i.C2350 <> d.C2350 OR i.C2351 <> d.C2351 OR i.C2352 <> d.C2352 
				OR i.C2353 <> d.C2353 OR i.C2354 <> d.C2354 OR i.C2355 <> d.C2355 OR i.C2356 <> d.C2356 OR i.C2357 <> d.C2357 
				OR i.C2358 <> d.C2358 OR i.C2359 <> d.C2359 OR i.C2360 <> d.C2360 OR i.C2361 <> d.C2361 OR i.C2363 <> d.C2363 
				OR i.C2344 <> d.C2344 OR i.C2345 <> d.C2345 OR i.C2346 <> d.C2346 OR i.C2347 <> d.C2347 OR i.C2364 <> d.C2364 
				OR i.C2365 <> d.C2365 OR i.C2366 <> d.C2366 OR i.C2367 <> d.C2367 OR i.C2368 <> d.C2368 OR i.C2369 <> d.C2369 
				OR i.C2370 <> d.C2370 OR i.C2371 <> d.C2371 OR i.C2372 <> d.C2372 OR i.C2373 <> d.C2373 OR i.C2374 <> d.C2374 
				OR i.C2375 <> d.C2375 OR i.C2376 <> d.C2376 OR i.C2377 <> d.C2377 OR i.C2378 <> d.C2378 OR i.C2379 <> d.C2379 
				OR i.C2380 <> d.C2380 OR i.C2381 <> d.C2381 OR i.C2382 <> d.C2382 OR i.C2383 <> d.C2383 OR i.C2384 <> d.C2384 
				OR i.C2385 <> d.C2385 OR i.C2386 <> d.C2386 OR i.C2387 <> d.C2387 OR i.C2388 <> d.C2388 OR i.C2389 <> d.C2389 
				OR i.C2390 <> d.C2390 OR i.C2391 <> d.C2391 OR i.C2392 <> d.C2392 OR i.C2393 <> d.C2393 OR i.C2394 <> d.C2394 
				OR i.C2395 <> d.C2395 OR i.C2396 <> d.C2396 OR i.cdsrc <> d.cdsrc OR i.CPUBLISH <> d.CPUBLISH
				OR i.CALDATE_DT2 <> d.CALDATE_DT2 OR i.DUEDATE_DT2 <> d.DUEDATE_DT2 OR i.C23DT2_1 <> d.C23DT2_1
				OR i.C23DT2_2 <> d.C23DT2_2	OR i.C23DATE_1 <> d.C23DATE_1 OR i.C23DATE_2 <> d.C23DATE_2
				OR i.C23TIME_1 <> d.C23TIME_1 OR i.C23TIME_2 <> d.C23TIME_2 OR i.C23FLT_1 <> d.C23FLT_1
				OR i.C23FLT_2 <> d.C23FLT_2 OR i.C23INT_1 <> d.C23INT_1 OR i.C23INT_2 <> d.C23INT_2
				OR i.C23NVAR_1 <> d.C23NVAR_1 OR i.C23NVAR_2 <> d.C23NVAR_2 OR i.C23NVAR_3 <> d.C23NVAR_3
				OR i.C23NVAR_4 <> d.C23NVAR_4 OR i.C23TEXT_1 <> d.C23TEXT_1

/* Begin of cal_update_logtime trigger   
** Set when & who modified columns
*/		
		--If C2338 exists, use it, otherwise use connected user name
		UPDATE #tmp_cal SET C2337 = CURRENT_TIMESTAMP, C2338 = COALESCE(C2338, REPLACE(SUBSTRING(SYSTEM_USER, CHARINDEX('\', SYSTEM_USER), LEN(SYSTEM_USER)), '\', ''))
			WHERE CTAG = ANY(SELECT ins_ctag from #watevs)
/* End of cal_update_logtime */


/* Begin cal_u_2309 trigger 
** Update total time column if tech1 or tech2 times changed
*/
		UPDATE #tmp_cal SET C2310 = COALESCE(C2324, 0) + COALESCE(C2328, 0) WHERE CTAG in (SELECT ins_ctag FROM #watevs WHERE ins_2324 <> del_2324 OR ins_2328 <> del_2328)

/* End cal_u_2309 */

/* Begin cal_rel_upd_trig */
		IF EXISTS (SELECT 1 FROM #tmp_cal i JOIN #watevs w on (i.CTAG = w.ins_ctag)
						WHERE COALESCE(combine,N'') <> N'CMB' AND (w.ins_2308 <> w.del_2308 OR w.ins_2323 <> w.del_2323))
			UPDATE i SET i.C2362 = 
				CASE i.C2308
					WHEN 'AS-FOUND' THEN 
						CASE
							WHEN i.C2323 = 'Y' THEN 
								CASE 
									WHEN COALESCE(c.C2362,0) < 1 THEN 1
									ELSE COALESCE(c.C2362,1) + 1
								END
							ELSE 
								CASE
									WHEN COALESCE(c.C2362,0) > -1 THEN -1
									ELSE COALESCE(c.C2362,0) - 1
								END
						END
					WHEN 'FOUND-LEFT' THEN 
						CASE
							WHEN i.C2323 = 'Y' THEN 
								CASE 
									WHEN COALESCE(c.C2362,0) < 1 THEN 1
									ELSE COALESCE(c.C2362,0) + 1
								END
							ELSE 
								CASE
									WHEN COALESCE(c.C2362,0) > -1 THEN -1
									ELSE COALESCE(c.C2362,0) - 1
								END
						END
					ELSE COALESCE(c.C2362,1)	
				END	
			FROM #tmp_cal i 
				JOIN mt.Calibration c ON (i.MTAG=c.MTAG) 
				JOIN #watevs w on (i.CTAG = w.ins_ctag) 
			WHERE c.C2339 = 1 AND COALESCE(i.combine,N'') <> N'CMB' AND (w.ins_2308 <> w.del_2308 OR w.ins_2323 <> w.del_2323)
/* End cal_rel_upd_trig  */


/*Use an update statement to move the changed data to the target table for all rows updated.  */
			UPDATE c
				SET c.C2301 = t.C2301,
					c.C2302 = t.C2302,
					c.C2303 = t.C2303,
					c.C2304 = t.C2304,
					c.C2306 = t.C2306,
					c.C2307 = t.C2307,
					c.C2309 = t.C2309,
					c.C2310 = t.C2310,
					c.C2311 = t.C2311,
					c.C2312 = t.C2312,
					c.C2313 = t.C2313,
					c.C2314 = t.C2314,
					c.C2316 = t.C2316,
					c.C2320 = t.C2320,
					c.C2321 = t.C2321,
					c.C2322 = t.C2322,
					c.C2323 = t.C2323,
					c.C2324 = t.C2324,
					c.C2326 = t.C2326,
					c.C2327 = t.C2327,
					c.C2328 = t.C2328,
					c.C2329 = t.C2329,
					c.C2330 = t.C2330,
					c.C2331 = t.C2331,
					c.C2332 = t.C2332,
					c.C2333 = t.C2333,
					c.C2334 = t.C2334,
					c.C2335 = t.C2335,
					c.C2336 = t.C2336,
					c.C2337 = t.C2337,
					c.C2338 = t.C2338,
					c.C2339 = t.C2339,
					c.C2341 = t.C2341,
					c.MTAG = t.MTAG,
					c.CTAG = t.CTAG,
					c.C2362 = t.C2362,
					c.C2308 = t.C2308,
					c.C2315 = t.C2315,
					c.C2342 = t.C2342,
					c.C2343 = t.C2343,
					c.C2317 = t.C2317,
					c.C2318 = t.C2318,
					c.C2319 = t.C2319,
					c.C2325 = t.C2325,
					c.C2350 = t.C2350,
					c.C2351 = t.C2351,
					c.C2352 = t.C2352,
					c.C2353 = t.C2353,
					c.C2354 = t.C2354,
					c.C2355 = t.C2355,
					c.C2356 = t.C2356,
					c.C2357 = t.C2357,
					c.C2358 = t.C2358,
					c.C2359 = t.C2359,
					c.C2360 = t.C2360,
					c.C2361 = t.C2361,
					c.C2363 = t.C2363,
					c.cdsrc = t.cdsrc,
					c.CPUBLISH = t.CPUBLISH,
					c.C2344 = t.C2344,
					c.C2345 = t.C2345,
					c.C2346 = t.C2346,
					c.C2347 = t.C2347,
					c.C2364 = t.C2364,
					c.C2365 = t.C2365,
					c.C2366 = t.C2366,
					c.C2367 = t.C2367,
					c.C2368 = t.C2368,
					c.C2369 = t.C2369,
					c.C2370 = t.C2370,
					c.C2371 = t.C2371,
					c.C2372 = t.C2372,
					c.C2373 = t.C2373,
					c.C2374 = t.C2374,
					c.C2375 = t.C2375,
					c.C2376 = t.C2376,
					c.C2377 = t.C2377,
					c.C2378 = t.C2378,
					c.C2379 = t.C2379,
					c.C2380 = t.C2380,
					c.C2381 = t.C2381,
					c.C2382 = t.C2382,
					c.C2383 = t.C2383,
					c.C2384 = t.C2384,
					c.C2385 = t.C2385,
					c.C2386 = t.C2386,
					c.C2387 = t.C2387,
					c.C2388 = t.C2388,
					c.C2389 = t.C2389,
					c.C2390 = t.C2390,
					c.C2391 = t.C2391,
					c.C2392 = t.C2392,
					c.C2393 = t.C2393,
					c.C2394 = t.C2394,
					c.C2395 = t.C2395,
					c.C2396 = t.C2396,
					c.combine = t.combine,
					c.CALDATE_DT2=t.CALDATE_DT2,
					c.DUEDATE_DT2=t.DUEDATE_DT2,
					c.C23DT2_1=t.C23DT2_1,
					c.C23DT2_2=t.C23DT2_2,
					c.C23DATE_1=t.C23DATE_1,
					c.C23DATE_2=t.C23DATE_2,
					c.C23TIME_1=t.C23TIME_1,
					c.C23TIME_2=t.C23TIME_2,
					c.C23FLT_1=t.C23FLT_1,
					c.C23FLT_2=t.C23FLT_2,
					c.C23INT_1=t.C23INT_1,
					c.C23INT_2=t.C23INT_2,
					c.C23NVAR_1=t.C23NVAR_1,
					c.C23NVAR_2=t.C23NVAR_2,
					c.C23NVAR_3=t.C23NVAR_3,
					c.C23NVAR_4=t.C23NVAR_4,
					c.C23TEXT_1=t.C23TEXT_1
			FROM Calibration AS c
				JOIN #tmp_cal as t
					ON t.ctag=c.ctag;

		COMMIT TRANSACTION;
		SET NOCOUNT OFF;
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 
			ROLLBACK TRANSACTION;
		THROW;

	END CATCH;
END

