USE [METX]
GO
/****** Object:  Trigger [mt].[ots_cl_inventory_delete]    Script Date: 9/24/2024 11:23:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [mt].[ots_cl_inventory_delete] 
	ON [mt].[Inventory] 
	AFTER DELETE
	AS
	
	BEGIN TRY

		SET NOCOUNT ON;

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

		SELECT @current_user = SUSER_SNAME();

		DECLARE @RowCount INTEGER,
		@I INTEGER;

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

		DECLARE @tmp_ots_cl_inventory_delete_d TABLE (
			ColumnID INT IDENTITY(1,1),
			[I4201] [nvarchar](30) NOT NULL,
			[I4202] [nvarchar](30) NULL,
			[I4203] [nvarchar](50) NULL,
			[I4204] [nvarchar](60) NULL,
			[I4205] [nvarchar](1) NULL,
			[I4206] [nvarchar](50) NULL,
			[I4207] [nvarchar](24) NULL,
			[I4208] [nvarchar](29) NULL,
			[I4209] [nvarchar](1) NOT NULL,
			[I4210] [nvarchar](12) NULL,
			[I4211] [nvarchar](15) NULL,
			[I4212] [date] NULL,
			[I4213] [nvarchar](12) NULL,
			[I4214] [nvarchar](16) NULL,
			[I4215] [nvarchar](5) NULL,
			[I4216] [nvarchar](5) NULL,
			[I4217] [nvarchar](60) NULL,
			[I4218] [nvarchar](4) NULL,
			[I4219] [numeric](12,2) NULL,
			[I4220] [int] NULL,
			[I4221] [date] NULL,
			[I4222] [nvarchar](12) NULL,
			[I4223] [nvarchar](16) NULL,
			[I4224] [date] NULL,
			[I4225] [nvarchar](8) NULL,
			[I4226] [nvarchar](1) NULL,
			[I4228] [nvarchar](1) NOT NULL,
			[I4229] [int] NULL,
			[I4230] [int] NULL,
			[I4231] [nvarchar](32) NULL,
			[I4232] [nvarchar](32) NULL,
			[I4233] [nvarchar](32) NULL,
			[I4234] [nvarchar](32) NULL,
			[I4235] [nvarchar](32) NULL,
			[I4236] [datetime2](7) NULL,
			[I4237] [nvarchar](16) NULL,
			[I4238] [datetime2](7) NULL,
			[I4239] [nvarchar](16) NULL,
			[I4240] [nvarchar](8) NOT NULL,
			[I4241] [nvarchar](80) NULL,
			[I4242] [nvarchar](80) NULL,
			[I4243] [nvarchar](80) NULL,
			[I4244] [nvarchar](48) NULL,
			[I4245] [nvarchar](48) NULL,
			[I4246] [nvarchar](48) NULL,
			[I4247] [nvarchar](48) NULL,
			[I4248] [nvarchar](50) NULL,
			[I4249] [nvarchar](48) NULL,
			[I4250] [date] NULL,
			[I4251] [date] NULL,
			[I4252] [date] NULL,
			[I4253] [date] NULL,
			[I4254] [numeric](12,2) NULL,
			[I4255] [bit] NOT NULL,
			[I4256] [numeric](12,2) NULL,
			[I4257] [numeric](12,2) NULL,
			[I4258] [nvarchar](1) NULL,
			[I4259] [int] NULL,
			[I4260] [nvarchar](4000) NULL,
			[I4261] [nvarchar](128) NULL,
			[I4262] [nvarchar](128) NULL,
			[I4263] [char](512) NULL,
			[I4299] [nvarchar](1) NOT NULL,
			[idsrc] [nvarchar](3) NULL,
			[IPUBLISH] [nvarchar](32) NULL,
			[ktag] [nvarchar](80) NULL,
			[MTAG] [nvarchar](80) NOT NULL,
			[replic] [nvarchar](1) NULL
			); 

		INSERT INTO @tmp_ots_cl_inventory_delete_d  
			SELECT I4201,
				I4202,
				I4203,
				I4204,
				I4205,
				I4206,
				I4207,
				I4208,
				I4209,
				I4210,
				I4211,
				I4212,
				I4213,
				I4214,
				I4215,
				I4216,
				I4217,
				I4218,
				I4219,
				I4220,
				I4221,
				I4222,
				I4223,
				I4224,
				I4225,
				I4226,
				I4228,
				I4229,
				I4230,
				I4231,
				I4232,
				I4233,
				I4234,
				I4235,
				I4236,
				I4237,
				I4238,
				I4239,
				I4240,
				I4241,
				I4242,
				I4243,
				I4244,
				I4245,
				I4246,
				I4247,
				I4248,
				I4249,
				I4250,
				I4251,
				I4252,
				I4253,
				I4254,
				I4255,
				I4256,
				I4257,
				I4258,
				I4259,
				I4260,
				I4261,
				I4262,
				I4263,
				I4299,
				idsrc,
				IPUBLISH,
				ktag,
				MTAG,
				replic
			FROM deleted;

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

		SET @I = 1;

		WHILE (@I <= @RowCount) 

			BEGIN

			SELECT @mtag=mtag, @I4201=I4201  FROM @tmp_ots_cl_inventory_delete_d WHERE ColumnID = @I;

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

			-- place old record into ots_cl_inventory
			INSERT INTO mt.ots_cl_inventory (
			I4201,
			I4202,
			I4203,
			I4204,
			I4205,
			I4206,
			I4207,
			I4208,
			I4209,
			I4210,
			I4211,
			I4212,
			I4213,
			I4214,
			I4215,
			I4216,
			I4217,
			I4218,
			I4219,
			I4220,
			I4221,
			I4222,
			I4223,
			I4224,
			I4225,
			I4226,
			I4228,
			I4229,
			I4230,
			I4231,
			I4232,
			I4233,
			I4234,
			I4235,
			I4236,
			I4237,
			I4238,
			I4239,
			I4240,
			I4241,
			I4242,
			I4243,
			I4244,
			I4245,
			I4246,
			I4247,
			I4248,
			I4249,
			I4250,
			I4251,
			I4252,
			I4253,
			I4254,
			I4255,
			I4256,
			I4257,
			I4258,
			I4259,
			I4260,
			I4261,
			I4262,
			I4263,
			I4299,
			idsrc,
			IPUBLISH,
			ktag,
			MTAG,
			replic)
		SELECT	
			I4201,
			I4202,
			I4203,
			I4204,
			I4205,
			I4206,
			I4207,
			I4208,
			I4209,
			I4210,
			I4211,
			I4212,
			I4213,
			I4214,
			I4215,
			I4216,
			I4217,
			I4218,
			I4219,
			I4220,
			I4221,
			I4222,
			I4223,
			I4224,
			I4225,
			I4226,
			I4228,
			I4229,
			I4230,
			I4231,
			I4232,
			I4233,
			I4234,
			I4235,
			I4236,
			I4237,
			I4238,
			I4239,
			I4240,
			I4241,
			I4242,
			I4243,
			I4244,
			I4245,
			I4246,
			I4247,
			I4248,
			I4249,
			I4250,
			I4251,
			I4252,
			I4253,
			I4254,
			I4255,
			I4256,
			I4257,
			I4258,
			I4259,
			i4260,
			i4261,
			i4262,
			I4263,
			I4299,
			idsrc,
			IPUBLISH,
			ktag,
			MTAG,
			replic
			FROM @tmp_ots_cl_inventory_delete_d WHERE ColumnID = @I;

			SET @I = @I + 1
			
		END

	SET NOCOUNT OFF;

END TRY

BEGIN CATCH

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

END CATCH;

