Discussion :

[stephane.julien]

Bonjour,

J'ai un TRIGGER qui est INSTEAD OF DELETE sur une table de log. Ce dernier archive un log avant de le supprimer réellement. J'aimerais sortir le code que j'exécute à l'intérieur du TRIGGER pour le mettre dans une procédure stockée afin de pouvoir l'exécuter depuis mon code .NET. Seulement, lorsque j'appelle ma procédure stockée, l'erreur suivante survient :

Les déclencheurs INSTEAD OF ne prennent pas en charge la récursivité directe. Échec de l'exécution du déclencheur.

C'est parce que je supprime le log après l'avoir archivé, ce qui appelle à nouveau le TRIGGER .

Y a-t-il un moyen de résoudre ce problème sans laisser le code dans le TRIGGER?

Merci d'avance...

[rudib]

Bonjour,

quelle version de SQL Server ?
Peux-tu poster le code ?

[stephane.julien]

SQL Server 2005 EXPRESS

TRIGGER :

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
31
ALTER TRIGGER [dbo].[Archive]
   ON  [dbo].[LogWork]
   INSTEAD OF DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
--Déclaration des variables locales
  DECLARE @l_iId int
 
  --Déclaration d'un curseur pour parcourir les éléments à archiver
  DECLARE IdCursor CURSOR FOR
    SELECT Id FROM DELETED
 
  --Parcours du curseur
  OPEN IdCursor;
  FETCH IdCursor INTO @l_iId;
  WHILE @@fetch_status = 0
  BEGIN
    -- Appel de la procédure stockée
    EXECUTE _LogWork_Archive @l_iId
 
    --Curseur suivant
    FETCH IdCursor INTO @l_iId;
  END
 
  --Fermeture du curseur
  CLOSE IdCursor;
  DEALLOCATE IdCursor;

Procédure stockée :

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
31
32
33
34
35
36
ALTER PROCEDURE [dbo].[_LogWork_Archive]
	-- Add the parameters for the stored procedure here
	@_iLogWorkId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Création d'une transaction
    BEGIN TRANSACTION
 
  --Archivage du log de travail
  INSERT INTO LogArchive(TimeLog, ...)
    SELECT LogWork.Time,...
    FROM  LogWork INNER JOIN
                      ...
    WHERE LogWork.Id = @_iLogWorkId
  IF @@ERROR <> 0
  BEGIN
     ROLLBACK TRANSACTION
     RAISERROR ('Impossible d''archiver le log',16,1)
     RETURN
  END
 
  --Suppression du log de travail
  DELETE FROM LogWork WHERE LogWork.Id = @_iLogWorkId
  IF @@ERROR <> 0
  BEGIN
     ROLLBACK TRANSACTION
     RAISERROR ('Impossible de supprimer le log de travail',16,4)
     RETURN
  END
 
  --Si on arrive ici, c'est que tout c'est bien passé. On COMMIT
  COMMIT TRANSACTION

[SQLpro]

Les suisses parlent aux suisses.... ! ;-)

A +

[rudib]

Tu es en effet en train de te mordre la queue : si tu supprimes, tu lances la procédure qui supprime, mais tu veux aussi lancer la procédure manuellement... si tu la lances, elle supprime, ce qui appelle le trigger qui appelle la procédure. Fais plus simple, sinon tu es mort.

Fais une procédure qui supprime, et enlève le trigger, ainsi que les privilèges de suppression sur la table directement.
Ou laisse le trigger sans procédure et sans curseur, et fais une autre procédure de suppression.

Pour éviter ta transaction :

Code :

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

1
2
3
4
DELETE
FROM LogWork 
OUTPUT Deleted.Time,... INTO LogArchive
WHERE LogWork.Id = @_iLogWorkId

[rudib]

Les suisses parlent aux suisses.... ! ;-)

Il es jurassien, c'est pas pareil

[stephane.julien]

Il es jurassien, c'est pas pareil

Pourquoi tu es suisse aussi? Effectivement jurassien c'est pas pareil, c'est mieux...

Revenons à nos moutons

Fais une procédure qui supprime, et enlève le trigger, ainsi que les privilèges de suppression sur la table directement.
Ou laisse le trigger sans procédure et sans curseur, et fais une autre procédure de suppression.

Peux-tu expliquer la solution numéro 1 stp? Pur curiosité, car je pense que je vais mettre le code dans mon trigger, puis dans ma procédure stockée, je vais directement faire :

Code :

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

DELETE FROM LogWork WHERE Id=@_iLogWorkId

Ce qui archivera automatiquement... C'est même la manière la plus logique!

Merci pour l'optimisation avec la clause OUTPUT. Cependant, comment faire si je l'utilise dans mon TRIGGER. J'ai une table DELETED qui est pour la clause OUTPUT, et une table DELETED pour les éléments qui doivent être supprimés par le TRIGGER... Deux même noms de table, comment les différencier?

Merci d'avance à Paris !

[rudib]

Merci pour l'optimisation avec la clause OUTPUT. Cependant, comment faire si je l'utilise dans mon TRIGGER. J'ai une table DELETED qui est pour la clause OUTPUT, et une table DELETED pour les éléments qui doivent être supprimés par le TRIGGER... Deux même noms de table, comment les différencier?

Je suis valaisan d'origine.

le DELETED correspondra au contexte de l'OUTPUT, pas de souci.

[stephane.julien]

Je suis valaisan d'origine.

Le problème, c'est que j'utilise le DELETED du TRIGGER dans mes requêtes actuelles.... Est-ce qu'il y a un moyen d'utiliser les deux DELETED ?

[rudib]

J'ai déja répondu : comme pour des variables qui portent le même nom, dans n'importe quel langage, il n'y a pas de collision. Quand tu utilises la pseudo table DELETED dans le contexte de l'UPDATE, c'est une pseudo table local à ton instruction. Tu ne peux utiliser que celle-là dans l'OUTPUT, et il n'y a pas de mélange avec la pseudo table DELETED de ton trigger.

[stephane.julien]

Ok merci de ces précisions.