SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: M. Jordaan -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[SB_AT_K_PLC8_Mesal_Ins] @inserted XML, @deleted XML = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --nothing to do? IF (@inserted IS NULL) AND (@deleted IS NULL) RETURN; -- variabelen voor waarden welke net gelogd zijn DECLARE @S013_CalcButtSilo40LV float; DECLARE @S040_GreenButtSilo14LV float; DECLARE @MesalDatumTijd datetime; --DECLARE @TijdTussenSamples float --DECLARE @DoCSUM INT = 1; DECLARE @NoCSUM INT = 0; DECLARE @HistLog INT = -1; DECLARE @rc INT; DECLARE @i INT; DECLARE @trancount INT; --SET @TijdTussenSamples = 5.0 SET @trancount = @@TRANCOUNT; BEGIN TRY -- Create temp table for inserted record(s) DECLARE @MASSA_K_PLC8 TABLE ( [ID] [INT] IDENTITY(1,1) PRIMARY KEY, [FL_SECTIME] [int] NULL, [LEEG] [float] NULL, [DATUM_TIJD] [char](19) NULL, [AI_1] [float] NULL, [AI_2] [float] NULL, [AI_4] [float] NULL, [AI_5] [float] NULL, [AI_6] [float] NULL, [AI_7] [float] NULL, [AI_8] [float] NULL, [AI_9] [float] NULL, [AI_10] [float] NULL, [AI_11] [float] NULL, [AI_12] [float] NULL, [AI_13] [float] NULL, [AI_14] [float] NULL, [AI_15] [float] NULL, [AI_16] [float] NULL, [AI_17] [float] NULL, [AI_18] [float] NULL, [AI_19] [float] NULL, [AI_20] [float] NULL, [AI_21] [float] NULL, [AI_22] [float] NULL, [AI_23] [float] NULL, [AI_24] [float] NULL, [AI_25] [float] NULL, [AI_26] [float] NULL, [AI_27] [float] NULL, [AI_28] [float] NULL, [AI_29] [float] NULL, [AI_30] [float] NULL, [AI_31] [float] NULL, [AI_32] [float] NULL, [AI_33] [float] NULL, [AI_34] [float] NULL, [AI_35] [float] NULL, [AI_36] [float] NULL, [AI_3] [float] NULL); INSERT INTO @MASSA_K_PLC8 ([FL_SECTIME], [LEEG], [DATUM_TIJD], [AI_1], [AI_2], [AI_4], [AI_5], [AI_6], [AI_7], [AI_8], [AI_9], [AI_10], [AI_11], [AI_12], [AI_13], [AI_14], [AI_15], [AI_16], [AI_17], [AI_18], [AI_19], [AI_20], [AI_21], [AI_22], [AI_23], [AI_24], [AI_25], [AI_26], [AI_27], [AI_28], [AI_29], [AI_30], [AI_31], [AI_32], [AI_33], [AI_34], [AI_35], [AI_36], [AI_3]) SELECT X.value('(row/FL_SECTIME/text())[1]', 'int') AS FL_SECTIME ,X.value('(row/LEEG/text())[1]', 'float') AS LEEG ,X.value('(row/DATUM_TIJD/text())[1]', 'char(19)') AS DATUM_TIJD ,X.value('(row/AI_1/text())[1]', 'float') AS AI_1 ,X.value('(row/AI_2/text())[1]', 'float') AS AI_2 ,X.value('(row/AI_4/text())[1]', 'float') AS AI_4 ,X.value('(row/AI_5/text())[1]', 'float') AS AI_5 ,X.value('(row/AI_6/text())[1]', 'float') AS AI_6 ,X.value('(row/AI_7/text())[1]', 'float') AS AI_7 ,X.value('(row/AI_8/text())[1]', 'float') AS AI_8 ,X.value('(row/AI_9/text())[1]', 'float') AS AI_9 ,X.value('(row/AI_10/text())[1]', 'float') AS AI_10 ,X.value('(row/AI_11/text())[1]', 'float') AS AI_11 ,X.value('(row/AI_12/text())[1]', 'float') AS AI_12 ,X.value('(row/AI_13/text())[1]', 'float') AS AI_13 ,X.value('(row/AI_14/text())[1]', 'float') AS AI_14 ,X.value('(row/AI_15/text())[1]', 'float') AS AI_15 ,X.value('(row/AI_16/text())[1]', 'float') AS AI_16 ,X.value('(row/AI_17/text())[1]', 'float') AS AI_17 ,X.value('(row/AI_18/text())[1]', 'float') AS AI_18 ,X.value('(row/AI_19/text())[1]', 'float') AS AI_19 ,X.value('(row/AI_20/text())[1]', 'float') AS AI_20 ,X.value('(row/AI_21/text())[1]', 'float') AS AI_21 ,X.value('(row/AI_22/text())[1]', 'float') AS AI_22 ,X.value('(row/AI_23/text())[1]', 'float') AS AI_23 ,X.value('(row/AI_24/text())[1]', 'float') AS AI_24 ,X.value('(row/AI_25/text())[1]', 'float') AS AI_25 ,X.value('(row/AI_26/text())[1]', 'float') AS AI_26 ,X.value('(row/AI_27/text())[1]', 'float') AS AI_27 ,X.value('(row/AI_28/text())[1]', 'float') AS AI_28 ,X.value('(row/AI_29/text())[1]', 'float') AS AI_29 ,X.value('(row/AI_30/text())[1]', 'float') AS AI_30 ,X.value('(row/AI_31/text())[1]', 'float') AS AI_31 ,X.value('(row/AI_32/text())[1]', 'float') AS AI_32 ,X.value('(row/AI_33/text())[1]', 'float') AS AI_33 ,X.value('(row/AI_34/text())[1]', 'float') AS AI_34 ,X.value('(row/AI_35/text())[1]', 'float') AS AI_35 ,X.value('(row/AI_36/text())[1]', 'float') AS AI_36 ,X.value('(row/AI_3/text())[1]', 'float') AS AI_3 FROM @inserted.nodes('inserted') AS T(X); --bewaar het aantal opgeslagen regels in de tijdelijke tabel SET @rc = @@ROWCOUNT; --SCOPE_IDENTITY() would also work IF (@rc = 0) THROW 50002, 'Logging into table variable failed', 1; END TRY BEGIN CATCH -- rethrow the error THROW; END CATCH -- Assume more then one row can be inserted for a trigger. -- haal actuele waarden op, FL logt standaard 1 record per keer -- maar kan uit back-log meerdere records in een keer sturen. SET @i = 1; WHILE @i <= @rc BEGIN BEGIN TRY -- fetch one row at a time SELECT @S013_CalcButtSilo40LV = AI_1, @S040_GreenButtSilo14LV = [AI_11] * 0.81, @MesalDatumTijd = CONVERT(datetime, DATUM_TIJD, 104) FROM @MASSA_K_PLC8 WHERE [ID] = @i ORDER BY [FL_SECTIME] ASC; END TRY BEGIN CATCH -- Error handling -- Select in temp table failed, record is not processed for MESAL -- update the rowcounter SET @i = @i + 1; CONTINUE; END CATCH -- update the rowcounter SET @i = @i + 1; BEGIN TRY IF @trancount = 0 BEGIN TRANSACTION; ELSE SAVE TRANSACTION SB_AT_K_PLC8_Mesal_Ins; --update mesal database, table EXEC master.dbo.msp_MesalUpdateTable 'S013_CalcButtSilo40LV', @S013_CalcButtSilo40LV, @MesalDatumTijd, @NoCSUM, @HistLog; EXEC master.dbo.msp_MesalUpdateTable 'S040_GreenButtSilo14LV', @S040_GreenButtSilo14LV, @MesalDatumTijd, @NoCSUM, @HistLog; IF @trancount = 0 COMMIT TRANSACTION; END TRY BEGIN CATCH -- Error handling DECLARE @xstate INT = XACT_STATE(); -- Rollback transaction, including mesal update(s) IF @xstate = -1 ROLLBACK TRANSACTION; IF @xstate = 1 AND @trancount = 0 ROLLBACK TRANSACTION; IF @xstate = 1 AND @trancount > 0 ROLLBACK TRANSACTION SB_AT_K_PLC8_Mesal_Ins; THROW; --rethrow the message END CATCH END --end of while loop END GO