IF  EXISTS (SELECT 1 FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[T_CREATE_DT472]'))
	DROP TRIGGER [dbo].[T_CREATE_DT472]
GO
CREATE TRIGGER dbo.T_CREATE_DT472
ON dbo.V_DT430_DT472
INSTEAD OF UPDATE
AS 
BEGIN
    SET  NOCOUNT  ON /* speed up operation by avoiding query of unnecessary info */
	DECLARE 
		@rowcount NUMERIC(10,0),
		@pos NUMERIC(10,0),
		@owner_license VARCHAR(4),
		@di VARCHAR(6),
        @count_liz NUMERIC(2,0),
		@counter_exists NUMERIC(2,0);
	DECLARE 
		@tmptable TABLE (
			PROJECT_ID VARCHAR(18), 
			TASK_ID VARCHAR(18), 
			RES_ID VARCHAR(20), 
			CAL_DATE DATETIME, 
			OWN_LIC VARCHAR(4),
			POS_ID NUMERIC(10,0) );
	SET @di = '001400';

-- update:
	UPDATE DT472 SET 
		DI001510 = BEL_IST,
		DI001501 = DATE_MODIFY,
		DI001504 = USER_MODIFY
	FROM INSERTED
	WHERE DI001395 = PR
		AND DI001516 = VG
		AND DI001517 = RESS
		AND DI001519 = DATUM_KAL
		AND (
		-- if there is exactly one row with a load for that day;
		(ABS(COALESCE(DI001510,0)) > 0.0009
		AND (
			SELECT COUNT(*) FROM DT472 
			WHERE DI001395 = PR
				AND DI001516   = VG
				AND DI001517   = RESS
				AND DI001519   = DATUM_KAL
				AND ABS(COALESCE(DI001510,0)) > 0.0009
			) = 1)
		OR (
		-- or if there is no row with a load for that day,
		SELECT COUNT(*) FROM DT472 WHERE DI001395 = PR
			AND DI001516   = VG
			AND DI001517   = RESS
			AND DI001519   = DATUM_KAL
			AND COALESCE(di001510,0) > 0.0009
		) = 0 AND (    
		-- and there is at least one row with no load for that day,
		-- take the row with the highest pos. no.
		SELECT COALESCE(MAX(DI001400),0) FROM DT472 WHERE DI001395 = PR
			AND DI001516   = VG
			AND DI001517   = RESS
			AND DI001519   = DATUM_KAL
			AND COALESCE(di001510,0) = 0
		) = DI001400)
	; 
	-- if there exists more than one row with a load, do nothing; 
	-- user has to distribute load to the data sets on his/her own.

-- finally, if there are no rows with a load yet, insert one, if BEL_IST <> 0:
-- As there are several rows possible, an extra handling of DI001400
-- (POS, as part of the table id) is necessary:

	-- get no. of relevant rows for insertion:
	INSERT INTO @tmptable (PROJECT_ID, TASK_ID, RES_ID, CAL_DATE, OWN_LIC)
	SELECT PR, VG, RESS, DATUM_KAL, OWNER_LICENSE FROM INSERTED
	-- only if load > 0
	WHERE COALESCE(BEL_IST,0) != 0 
		AND (-- and only if no row with a load for that day already exists:
			SELECT COUNT(*) FROM DT472 WHERE DI001395 = PR
				AND DI001516   = VG
				AND DI001517   = RESS
				AND DI001519   = DATUM_KAL
				AND ABS(COALESCE(DI001510,0)) > 0.0009
			) = 0
	;
	SELECT @rowcount = COUNT(*), @owner_license = MAX(OWN_LIC) FROM @tmptable;
    
    -- check for ownerlicence
	SET @count_liz = (SELECT COUNT(*) from dt401 WHERE DI026548 = 1)
	
	IF @count_liz > 1
		RAISERROR ('More than 1 active licence found in DT401.',
               18,
               1
			   );
	
	SET @owner_license = (SELECT DI000001 from dt401 WHERE DI026548 = 1)
	
    IF @rowcount > 0
	BEGIN
		
		SELECT @counter_exists = COUNT(*) FROM DT820 WHERE di008199 = @di AND DI008198 = @owner_license;
        IF @counter_exists = 0
            BEGIN
				SET @pos = 1;
				insert into dt820 (DI008198, DI008199, DI008202, DI008203,  DI008204, DI008205, DI008206, DI008207, DI008208, DI008209, DI008210, DI008211, DI008212, DI008213)
                       values (@owner_license, '001400', GETDATE(), GETDATE(), 7, 'MIGRATION', 'MIGRATION', @owner_license, Null, 7, 7, 7, 1, 0);            
			END
		ELSE 
			SELECT @pos = CAST(DI008212 AS NUMERIC(10,0)) FROM DT820 WITH (ROWLOCK, UPDLOCK) WHERE DI008199 = @di AND DI008198 = @owner_license;

        
		UPDATE DT820 SET DI008212 = REPLICATE('0',9-LEN(CAST(@pos + @rowcount AS VARCHAR)))+CAST(@pos + @rowcount AS VARCHAR) 
		WHERE DI008199 = @di AND DI008198 = @owner_license;
		-- generate the POS id:
		UPDATE @tmptable SET @pos = POS_ID = @pos + 1;

		INSERT INTO DT472
		(
			DI001395,
			DI001516,
			DI001517,
			DI001518,
			DI001400,
			DI001519,
			DI001510,
			DI001438,
			DI001445,
			DI007783,
			DI001500,
			DI001501,
			DI001502,
			DI001503,
			DI001504,
			DI001505,
			DI000602,
			DI001506,
			DI001507,
			DI001508
		)
		SELECT 
			PR,
			VG,
			RESS,
			KoA,
			POS_ID,
			DATUM_KAL,
			BEL_IST,
			'',
			OE_RESS,
			OE_RESS,
			DATE_MODIFY, -- modify date is create date for load record:
			DATE_MODIFY,
			RWDU,
			USER_MODIFY, -- modify user is create user for load record:
			USER_MODIFY,
			OWNER_LICENSE,
			OSC,
			OWNER,
			"GROUP",
			"ANY"
		FROM INSERTED INNER JOIN @tmptable ON
			PR = PROJECT_ID
			AND VG = TASK_ID
			AND RESS = RES_ID
			AND DATUM_KAL = CAL_DATE
		;
	END
END
GO
