Appel de procédure stockée depuis trigger

Version imprimable

30/09/2008, 09h47
stephane.julien

Appel de procédure stockée depuis trigger

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 :

Citation:

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...
30/09/2008, 10h05
rudib

Bonjour,

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

SQL Server 2005 EXPRESS

TRIGGER :

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
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:

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

30/09/2008, 10h17
SQLpro

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

A +
30/09/2008, 10h23
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:

1 2 3 4 DELETE FROM LogWork OUTPUT Deleted.Time,... INTO LogArchive WHERE LogWork.Id = @_iLogWorkId
30/09/2008, 10h24
rudib

Citation:

Envoyé par SQLpro

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

Il es jurassien, c'est pas pareil ;)
30/09/2008, 10h45
stephane.julien

Citation:

Envoyé par rudib

Il es jurassien, c'est pas pareil ;)

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

Revenons à nos moutons:)

Citation:

Envoyé par rudib

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:

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 ! ;)
30/09/2008, 11h15
rudib

Citation:

Envoyé par stephane.julien

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.
30/09/2008, 16h20
stephane.julien

Citation:

Envoyé par rudib

Je suis valaisan d'origine.

:king:

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 ?
30/09/2008, 16h25
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.
30/09/2008, 16h26
stephane.julien

Ok merci de ces précisions.