Déclenchement recursif trigger INSTEAD OF

Version imprimable

04/04/2013, 20h41
senftarnaud

Déclenchement recursif trigger INSTEAD OF

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
05/04/2013, 09h58
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
05/04/2013, 11h34
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?
05/04/2013, 12h04
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
05/04/2013, 12h05
aieeeuuuuu

Citation:

Envoyé par 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.

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.

Citation:

Envoyé par senftarnaud

"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

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:

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

Citation:

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

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

Code:

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

05/04/2013, 14h43
aieeeuuuuu

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