1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
|
/* Création de la table temporaire */
CREATE TABLE #l_Tab2Processing_Action_Execution
(
id_tab INT IDENTITY(0 , 1),
id_key_Action INT,
id_key_Trigger INT,
id_key_Trigger_Action_Order BIGINT,
id_Actions_Type INT
)
--========================================================================--
-- récupération des X premières actions à traiter
--========================================================================--
/* Récupérations des actions */
SET @l_nvc_SQLQuery = 'SELECT TOP ' + CAST(@l_i_nb_ActionsToThreat AS NVARCHAR) + ' [VTA].[id_key_Action],
[VTA].[id_key_Trigger],
[VAO].[id_key_Trigger_Action_Order],
VA.[id_Actions_Type]
FROM [dbo].[VTriggers_Actions_Orders] AS VAO
INNER JOIN [dbo].[VTriggers_Actions] AS VTA
ON [VAO].[id_key_Trigger_Action] = [VTA].[id_Key_Trigger_Action]
Inner Join [dbo].[VActions] AS VA
ON [VTA].[id_key_Action] = [VA].[id_key_Action]
left JOIN [dbo].[VTriggers_Actions_Orders_States] AS VAOS
ON [VAO].[id_key_Trigger_Action_Order] = [VAOS].[id_key_Trigger_Action_Order]
WHERE [VAOS].[id_StateType] = 14 AND [id_State] not in (4,45)
AND va.[id_Actions_Type] not in (0,1,2)
AND VTA.IsActive = 1
ORDER BY VAO.Trigger_Action_Order_Starting_Datetime'
INSERT INTO [#l_Tab2Processing_Action_Execution]
(
[id_key_Action],
[id_key_Trigger],
[id_key_Trigger_Action_Order],
[id_Actions_Type]
)
EXEC [sys].[sp_executesql] @l_nvc_SQLQuery
SET @l_i_Nb_Actions = @@ROWCOUNT
--========================================================================--
-- Application du traitement pour chaque actions
--========================================================================--
WHILE @l_i_Cpt_Actions < @l_i_Nb_Actions
BEGIN
/* S'il n'y a pas de transactions d'ouvertes */
IF(@@TRANCOUNT = 0)
BEGIN
/* On ouvre une transaction */
BEGIN TRANSACTION
/* On précise que l'on peut utiliser les transactions */
SET @l_b_UseTransaction = 1
END
/*Récupération des id liés à l'action*/
SELECT @l_i_id_key_Action = LTPAE.[id_key_Action],
@l_i_id_key_Trigger = LTPAE.[id_key_Trigger],
@l_bi_id_key_Trigger_Action_Order = LTPAE.[id_key_Trigger_Action_Order],
@l_i_id_Action_Type = LTPAE.[id_Actions_Type]
FROM [#l_Tab2Processing_Action_Execution] AS LTPAE
WHERE id_tab = @l_i_Cpt_Actions
SET @l_nvc_SQLParameters = '@l_i_id_Dossier_Current int, @l_i_id_State tinyint OUTPUT, @l_smi_id_Error smallint OUTPUT, @l_smi_id_ErrorSeverity smallint OUTPUT, @l_vc_ErrorMessage varchar(500) OUTPUT'
/* Execécution de la procédure */
SET @l_nvc_SQLQuery = 'EXECUTE [' + @l_vc_ServerName + '].' + @l_vc_DBName + '.dbo.SPManage_AccordingParameters
@a_i_id_key_Action = @l_i_id_key_Action,
@a_i_id_State = @l_i_id_State OUTPUT,
@a_smi_id_Error = @l_smi_id_Error OUTPUT,
@a_smi_id_ErrorSeverity = @l_smi_id_ErrorSeverity OUTPUT,
@a_vc_ErrorMessage = @l_vc_ErrorMessage OUTPUT'
EXECUTE sys.sp_executesql @l_nvc_SQLQuery, @l_nvc_SQLParameters,
@l_i_id_Dossier_Current = @l_i_id_Dossier_Current,
@l_i_id_State = @l_i_id_State OUTPUT,
@l_smi_id_Error = @l_smi_id_Error OUTPUT,
@l_smi_id_ErrorSeverity = @l_smi_id_ErrorSeverity OUTPUT,
@l_vc_ErrorMessage = @l_vc_ErrorMessage OUTPUT
IF @l_smi_id_Error <> 0 OR @l_smi_id_ErrorSeverity >= 300
GOTO Erreur
/* Si l'on peut utiliser les transactions */
IF @l_b_UseTransaction = 1
BEGIN
/* Pas d'Erreur : fin de la transaction */
COMMIT TRANSACTION
SET @l_b_UseTransaction = 0
END
/* Gestion de l'erreur */
Erreur:
/* Si l'on peut utiliser les transactions */
IF @l_b_UseTransaction = 1
BEGIN
/* On annule la transaction */
ROLLBACK TRANSACTION
SET @l_b_UseTransaction = 0
END
SET @l_i_Cpt_Actions += 1
END |
Partager