Discussion :

[senftarnaud]

Bonjour,

Je planche depuis un moment sur ce problème:

J'ai un trigger INSTEAD OF, d'API de table, qui me permet d'attraper les requêtes insert et de les modifier à ma guise.

Ce trigger fait appel à une procédure stockée qui ré-insert sur cette même table.

Le trigger se re-déclenche.

Comment évite cela?

J'ai testé avec trigger_nestlevel comme ceci:

IF trigger_nestlevel(object_ID('tr_io_insert_employes'),'IOT','DML') < 1

Malheureusement je constate que celui-ci me retourne 1 dès le premier déclenchement. Si je mets la valeur de test à <=1 j'obtiens une erreur : INSTEAD OF triggers do not support direct recursion. The trigger execution failed.

J'ai essayé mettre RECURSIVE_TRIGGERS à OFF mais rien n'y fait.

J'ai peut-être une lacune ou je fais quelque chose de faux c'est pourquoi je sollicite encore une fois votre aide en vous remerciant d'avance

[aieeeuuuuu]

Bonjour,

Vous avez plusieurs solutions :
- Appeler directement votre procédure stockée pour insérer des données dans cette table

- Créer un vue, et y placer votre trigger

- effectuer directement le traitement (celui de la procédure stockée) dans votre trigger

[senftarnaud]

Merci pour vos propositions.

Pour ce qui est :
"Appeler directement votre procédure stockée pour insérer des données dans cette table" c'est ce que je fais actuellement si j'ai bien compris votre proposition.

"Créer un vue, et y placer votre trigger" Je ne peux malheureusement pas car des applications utilisent déjà les tables.

"effectuer directement le traitement (celui de la procédure stockée) dans votre trigger" je ne peux pas nom plus car j'ai des traitement et gestions d'exception dans cette procédure qui doivent être vérifiés afin d'effectuer ou non un insert.

Je continue de chercher un moyen mais je commence à être à court.

Quelqu'un a-t-il encore d'autres idées?

[senftarnaud]

Et ce que je trouve bizarre c'est que j'ai beau essayé de désactiver la récursivité avec la commande suivante et rien n'y fait.
ALTER DATABASE MyDataBase SET RECURSIVE_TRIGGERS OFF
GO

[aieeeuuuuu]

Merci pour vos propositions.

Pour ce qui est :
"Appeler directement votre procédure stockée pour insérer des données dans cette table" c'est ce que je fais actuellement si j'ai bien compris votre proposition.

Non, quand je disais directement, je voulais dire "directement depuis votre application" : donc ne pas faire un insert, mais appeler la procédure stockée avec les données à nettoyer et insérer.

"effectuer directement le traitement (celui de la procédure stockée) dans votre trigger" je ne peux pas nom plus car j'ai des traitement et gestions d'exception dans cette procédure qui doivent être vérifiés afin d'effectuer ou non un insert.

Que faites vous exactement ? vous pouvez mettre ce traitement d'exception dans le trigger

[senftarnaud]

Effectivement vous avez raison je viens de tester et je n'ai plus d'erreur de récursivité par contre rien ne s'insert dans ma table de sauvegarde des exceptions:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
BEGIN CATCH
        DECLARE
        @error_line INT,
        @error_message NVARCHAR(2048),
        @error_number INT,
        @error_procedure NVARCHAR(126),
        @error_severity INT,
        @error_state INT;
 
        SET @error_line = ERROR_LINE()
        SET @error_message = ERROR_MESSAGE()
        SET @error_number = ERROR_NUMBER()
        SET @error_procedure = ERROR_PROCEDURE()
        SET @error_severity = ERROR_SEVERITY()
        SET @error_state = ERROR_STATE()
 
        PRINT(@error_message)
 
        EXEC [dbo].[save_exception]
            @error_line,
            @error_message,
            @error_number,
            @error_procedure,
            @error_severity,
            @error_state;
 
        RAISERROR(@error_message, @error_severity, @error_state,@error_number);
    END CATCH
END

Et j'obtiens la traditionnelle erreur

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

[senftarnaud]

Et voici ma procédure de sauvegarde de l'exception:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
ALTER PROCEDURE [dbo].[save_exception](
    @line INT,
    @message NVARCHAR(2048),
    @number INT,
    @procedure NVARCHAR(126),
    @severity INT,
    @state INT)
AS
BEGIN
    INSERT INTO [dbo].[errors_jn](
        error_line,
        error_message,
        error_number,
        error_procedure,
        error_severity,
        error_state,
        jn_dateTime,
        jn_session,
        jn_user)
    VALUES(
        @line,
        @message,
        @number,
        @procedure,
        @severity,
        @state,
        GETDATE(),
        @@SPID,
        SYSTEM_USER)
END

[aieeeuuuuu]

Faites un ROLLBACK dans le catch (avant l'appel de votre procédure stockée)