USE [METX]
GO
/****** Object:  Trigger [mt].[ots_cl_calibration_delete]    Script Date: 9/24/2024 11:45:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [mt].[ots_cl_calibration_delete] 
	ON [mt].[Calibration] 
	AFTER DELETE 
	AS 

	BEGIN TRY

		SET NOCOUNT ON;

		DECLARE @assetnum CHAR(60),
		@mtag CHAR(80),
		@ctag CHAR(80),
		@current_user CHAR(60);

		SET @current_user = SUSER_SNAME();

	DECLARE @RowCount INTEGER,
	@I INTEGER;

	SELECT  @RowCount=count(*) FROM deleted;
	IF @RowCount = 0
		RETURN;

	DECLARE @tmp_ots_cl_calibration_delete_deleted TABLE (
		ColumnID INT IDENTITY(1,1),
		[C2301] [date] NOT NULL,
		[C2302] [int] NULL,
		[C2303] [date] NULL,
		[C2304] [int] NULL,
		[C2306] [date] NULL,
		[C2307] [nvarchar](20) NULL,
		[C2308] [nvarchar](10) NULL,
		[C2309] [numeric](12,2) NULL,
		[C2310] [numeric](5,1) NULL,
		[C2311] [numeric](6,2) NULL,
		[C2312] [numeric](6,2) NULL,
		[C2313] [nvarchar](15) NULL,
		[C2314] [nvarchar](16) NULL,
		[C2315] [numeric](5,1) NULL,
		[C2316] [date] NULL,
		[C2317] [numeric](5,1) NULL,
		[C2318] [nvarchar](13) NULL,
		[C2319] [nvarchar](1) NULL,
		[C2320] [nvarchar](128) NULL,
		[C2321] [nvarchar](55) NULL,
		[C2322] [nvarchar](1) NULL,
		[C2323] [nvarchar](1) NULL,
		[C2324] [numeric](5,1) NULL,
		[C2325] [nvarchar](1) NULL,
		[C2326] [nvarchar](44) NULL,
		[C2327] [nvarchar](20) NULL,
		[C2328] [numeric](5,1) NULL,
		[C2329] [nvarchar](44) NULL,
		[C2330] [nvarchar](10) NULL,
		[C2331] [nvarchar](1) NOT NULL,
		[C2332] [int] NULL,
		[C2333] [nvarchar](8) NOT NULL,
		[C2334] [nvarchar](8) NULL,
		[C2335] [datetime2](7) NULL,
		[C2336] [nvarchar](16) NULL,
		[C2337] [datetime2](7) NULL,
		[C2338] [nvarchar](16) NULL,
		[C2339] [int] NULL,
		[C2341] [nvarchar](254) NULL,
		[C2342] [date] NULL,
		[C2343] [nvarchar](8) NULL,
		[C2344] [int] NULL,
		[C2345] [int] NULL,
		[C2346] [int] NULL,
		[C2347] [int] NULL,
		[C2350] [int] NULL,
		[C2351] [int] NULL,
		[C2352] [int] NULL,
		[C2353] [int] NULL,
		[C2354] [nvarchar](64) NULL,
		[C2355] [nvarchar](64) NULL,
		[C2356] [nvarchar](64) NULL,
		[C2357] [nvarchar](64) NULL,
		[C2358] [nvarchar](64) NULL,
		[C2359] [nvarchar](512) NULL,
		[C2360] [nvarchar](64) NULL,
		[C2361] [nvarchar](64) NULL,
		[C2362] [int] NULL,
		[C2363] [nvarchar](1) NULL,
		[C2364] [nvarchar](48) NULL,
		[C2365] [nvarchar](80) NULL,
		[C2366] [nvarchar](50) NULL,
		[C2367] [nvarchar](48) NULL,
		[C2368] [nvarchar](48) NULL,
		[C2369] [nvarchar](48) NULL,
		[C2370] [nvarchar](48) NULL,
		[C2371] [date] NULL,
		[C2372] [date] NULL,
		[C2373] [date] NULL,
		[C2374] [date] NULL,
		[C2375] [numeric](12,2) NULL,
		[C2376] [numeric](12,2) NULL,
		[C2377] [numeric](12,2) NULL,
		[C2378] [numeric](12,2) NULL,
		[C2379] [numeric](12,2) NULL,
		[C2380] [date] NULL,
		[C2381] [nvarchar](8) NULL,
		[C2382] [nvarchar](1) NULL,
		[C2383] [int] NULL,
		[C2384] [nvarchar](4000) NULL,
		[C2385] [nvarchar](128) NULL,
		[C2386] [numeric](5,1) NULL,
		[C2387] [numeric](5,1) NULL,
		[C2388] [numeric](9,2) NULL,
		[C2389] [numeric](7,2) NULL,
		[C2390] [numeric](9,2) NULL,
		[C2391] [numeric](7,2) NULL,
		[C2392] [numeric](2,0) NULL,
		[C2393] [nvarchar](1) NULL,
		[C2394] [nvarchar](1) NULL,
		[C2395] [nvarchar](10) NULL,
		[C2396] [nvarchar](16) NULL,
		[cdsrc] [nvarchar](3) NULL,
		[combine] [nvarchar](3) NULL,
		[CPUBLISH] [nvarchar](32) NULL,
		[CTAG] [nvarchar](80) NOT NULL,
		[MTAG] [nvarchar](80) NOT NULL
		); 

	INSERT INTO @tmp_ots_cl_calibration_delete_deleted  
		SELECT C2301,
		C2302,
		C2303,
		C2304,
		C2306,
		C2307,
		C2308,
		C2309,
		C2310,
		C2311,
		C2312,
		C2313,
		C2314,
		C2315,
		C2316,
		C2317,
		C2318,
		C2319,
		C2320,
		C2321,
		C2322,
		C2323,
		C2324,
		C2325,
		C2326,
		C2327,
		C2328,
		C2329,
		C2330,
		C2331,
		C2332,
		C2333,
		C2334,
		C2335,
		C2336,
		C2337,
		C2338,
		C2339,
		C2341,
		C2342,
		C2343,
		c2344,
		c2345,
		C2346,
		C2347,
		C2350,
		C2351,
		C2352,
		C2353,
		C2354,
		C2355,
		C2356,
		C2357,
		C2358,
		C2359,
		C2360,
		C2361,
		C2362,
		C2363,
		C2364,
		C2365,
		C2366,
		C2367,
		C2368,
		C2369,
		C2370,
		C2371,
		C2372,
		C2373,
		C2374,
		C2375,
		C2376,
		C2377,
		C2378,
		C2379,
		C2380,
		C2381,
		C2382,
		C2383,
		C2384,
		C2385,
		C2386,
		C2387,
		C2388,
		C2389,
		C2390,
		C2391,
		C2392,
		C2393,
		C2394,
		C2395,
		C2396,
		cdsrc,
		combine,
		CPUBLISH,
		CTAG,
		MTAG 
		FROM deleted;

	SET @RowCount = (SELECT COUNT(*) FROM @tmp_ots_cl_calibration_delete_deleted);

	SET @I = 1;

	WHILE (@I <= @RowCount) 

		BEGIN

			SELECT @mtag=mtag, @ctag=ctag FROM @tmp_ots_cl_calibration_delete_deleted WHERE ColumnID = @I;

			SELECT @assetnum = (SELECT i4201 FROM mt.inventory WHERE MTAG = @mtag);

			-- call log insert procedure for this row
			EXEC [mt].[ots_cl_log_insert] @current_user,@assetnum,NULL,@ctag,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'DEL',NULL,NULL,NULL;

			-- place old record into ots_cl_calibration
			INSERT INTO mt.ots_cl_calibration (
				C2301,
				C2302,
				C2303,
				C2304,
				C2306,
				C2307,
				C2308,
				C2309,
				C2310,
				C2311,
				C2312,
				C2313,
				C2314,
				C2315,
				C2316,
				C2317,
				C2318,
				C2319,
				C2320,
				C2321,
				C2322,
				C2323,
				C2324,
				C2325,
				C2326,
				C2327,
				C2328,
				C2329,
				C2330,
				C2331,
				C2332,
				C2333,
				C2334,
				C2335,
				C2336,
				C2337,
				C2338,
				C2339,
				C2341,
				C2342,
				C2343,
				C2344,
				C2345,
				C2346,
				C2347,
				C2350,
				C2351,
				C2352,
				C2353,
				C2354,
				C2355,
				C2356,
				C2357,
				C2358,
				C2359,
				C2360,
				C2361,
				C2362,
				C2363,
				C2364,
				C2365,
				C2366,
				C2367,
				C2368,
				C2369,
				C2370,
				C2371,
				C2372,
				C2373,
				C2374,
				C2375,
				C2376,
				C2377,
				C2378,
				C2379,
				C2380,
				C2381,
				C2382,
				C2383,
				C2384,
				C2385,
				C2386,
				C2387,
				C2388,
				C2389,
				C2390,
				C2391,
				C2392,
				C2393,
				C2394,
				C2395,
				C2396,
				cdsrc,
				combine,
				CPUBLISH,
				CTAG,
				MTAG) 
			SELECT C2301,
			C2302,
			C2303,
			C2304,
			C2306,
			C2307,
			C2308,
			C2309,
			C2310,
			C2311,
			C2312,
			C2313,
			C2314,
			C2315,
			C2316,
			C2317,
			C2318,
			C2319,
			C2320,
			C2321,
			C2322,
			C2323,
			C2324,
			C2325,
			C2326,
			C2327,
			C2328,
			C2329,
			C2330,
			C2331,
			C2332,
			C2333,
			C2334,
			C2335,
			C2336,
			C2337,
			C2338,
			C2339,
			C2341,
			C2342,
			C2343,
			c2344,
			c2345,
			C2346,
			C2347,
			C2350,
			C2351,
			C2352,
			C2353,
			C2354,
			C2355,
			C2356,
			C2357,
			C2358,
			C2359,
			C2360,
			C2361,
			C2362,
			C2363,
			C2364,
			C2365,
			C2366,
			C2367,
			C2368,
			C2369,
			C2370,
			C2371,
			C2372,
			C2373,
			C2374,
			C2375,
			C2376,
			C2377,
			C2378,
			C2379,
			C2380,
			C2381,
			C2382,
			C2383,
			C2384,
			C2385,
			C2386,
			C2387,
			C2388,
			C2389,
			C2390,
			C2391,
			C2392,
			C2393,
			C2394,
			C2395,
			C2396,
			cdsrc,
			combine,
			CPUBLISH,
			CTAG,
			MTAG 
			FROM @tmp_ots_cl_calibration_delete_deleted WHERE @RowCount = @i; 

		SET @I = @I + 1

		END

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

    THROW;      --  Uncomment only if you need to see the error in debug or development;

END CATCH;

