SQL SERVEUR 2005 et Pb TRIGGER

Version imprimable

Bonjour à tous,

Objectif : Créer un trigger qui met à 0 le champ Réserv UNIQUEMENT SUR l'enregistrement MODIFIE lorsque ce dernier est négatif.

Pourriez vous m'expliquer pourquoi ce trigger me renvoie le message ? --> Le niveau maximal d'imbrication des procedures stockées, des fonctions, des déclencheurs, ou des vues est dépassé (limite 32).
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 ALTER TRIGGER [dbo].[TransfertEgalZero] ON [dbo].[Magasin_P_Finis] AFTER UPDATE,INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE [Magasin_P_Finis] set transfert=0 where transfert < 0 END
J'ai tenté de corrigé de cette manière :
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 ALTER TRIGGER [dbo].[StockReserveEgalZero] ON [dbo].[Magasin_P_Finis] AFTER UPDATE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @NAuto Integer declare @Nbr Integer select @NAuto = [Auto] from inserted select @nbr = [réserv] from inserted if @Nbr<0 begin UPDATE [Magasin_P_Finis] set [réserv]=0 where [Auto] = @NAuto end END
- Est ce bon ?

- Est ce de cette manière que l'on récupère la valeur des champs de la ligne modifiée ?

- Dans la ligne : select @nbr = [réserv] from inserted
J'aurai préféré utiliser 'Updated' puisque c'est un trigger after Update mais j'ai un message d'erreur en retour.
Cela semble fonctionner quand même. pourquoi ?

Par avance, merci de toutes vos réponses.

Cyrille.

15/12/2008, 09h55
Aka Guymelef

Bonjour,

l'objet "updated" n'existe pas dans le monde des trigger de SQL Server. Tu n'as que le inserted (ligne ajoutée) et le deleted (ligne supprimée). Dans le cas d'un update, inserted représente la ou les lignes après modification tandis que deleted représente la ou les lignes avant modification.

Dès lors ici ton problème est que tu boucle récursivement sur tes triggers, en effet comme tu fais un update de la même table à l'intérieur du trigger, tu déclenche à nouveau le trigger et ainsi de suite.

Pour faire ça, je pense que tu dois t'orienter vers les instructions DISABLE TRIGGER et ENABLE TRIGGER.

Tu devrais plutôt écrire ton trigger comme ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 ALTER TRIGGER [dbo].[TransfertEgalZero] ON [dbo].[Magasin_P_Finis] AFTER UPDATE,INSERT AS SET NOCOUNT ON UPDATE [Magasin_P_Finis] set [Magasin_P_Finis].transfert=0 from [Magasin_P_Finis] inner join INSERTED on INSERTED.Auto = [Magasin_P_Finis].Auto where INSERTED.transfert < 0
De cette façon, tu ne met à jour que les lignes qui ont été modifiées, et uniquement celles qui ont transfert<0. Il n'y a alors aucune raison que le trigger se déclenche en cascade.

D'ailleurs c'est bizarre, il me semblait que dans la configuration par défaut de SQL Server, les triggers ne se déclenchaient pas en cascade. Cette option a dû changer, je vois qu'en SQL 2008 elle est bien activée par défaut.

Suite à reflexion, je pense que message d'erreur apparaissait parce que j'avais 2 triggers :
Le TRIGGER1 : (mise à 0 du champ [réserv] si ce dernier est négatif.

Code:

1
2
3
4
5
6
7
8
9
10
ALTER TRIGGER [dbo].[ReservEgalZero] 
   ON  [dbo].[Magasin_P_Finis]
   AFTER UPDATE,INSERT
AS 
  SET NOCOUNT ON
 
  UPDATE [Magasin_P_Finis] 
  SET [Magasin_P_Finis].réserv=0
  FROM [Magasin_P_Finis] INNER JOIN INSERTED ON INSERTED.Auto = [Magasin_P_Finis].Auto
  WHERE INSERTED.réserv < 0

Le TRIGGER2 : (mise à 0 du champ [transfert] si ce dernier est négatif.

Code:

1
2
3
4
5
6
7
8
9
10
ALTER TRIGGER [dbo].[TransfertEgalZero] 
   ON  [dbo].[Magasin_P_Finis]
   AFTER UPDATE,INSERT
AS 
  SET NOCOUNT ON
 
  UPDATE [Magasin_P_Finis] 
  SET [Magasin_P_Finis].transfert=0
  FROM [Magasin_P_Finis] INNER JOIN INSERTED ON INSERTED.Auto = [Magasin_P_Finis].Auto
  WHERE INSERTED.transfert < 0

Un sans l'autre fonctionne mais les deux en même temps générent une erreur (limite 32).

J'ai solutionné en remettant tout en un seul trigger. cela semble fonctionner mais le code suivant est il correct ?

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
ALTER TRIGGER [dbo].[EgalZero] 
   ON  [dbo].[Magasin_P_Finis]
   AFTER UPDATE,INSERT
AS 
Declare @Trans integer
Declare @res integer
 
  SET NOCOUNT ON
 
select @Trans=transfert from inserted;
select @res=réserv from inserted;
 
if @Trans < 0 
begin
  UPDATE [Magasin_P_Finis] 
  SET [Magasin_P_Finis].transfert=0
  FROM [Magasin_P_Finis] INNER JOIN INSERTED ON INSERTED.Auto = [Magasin_P_Finis].Auto
end
 
if @res < 0 
begin
  UPDATE [Magasin_P_Finis] 
  SET [Magasin_P_Finis].réserv=0
  FROM [Magasin_P_Finis] INNER JOIN INSERTED ON INSERTED.Auto = [Magasin_P_Finis].Auto
end

Citation:

Suite à reflexion, je pense que message d'erreur apparaissait parce que j'avais 2 triggers
...
Un sans l'autre fonctionne mais les deux en même temps générent une erreur (limite 32).

D'une façon générale, il vaut mieux éviter ce genre de chose et garder un seul trigger de chaque type (AFTER INSERT, AFTER UPDATE, AFTER DELETE) par table.
Quand on crée plusieurs triggers, on ne maîtrise pas l'ordre dans lequel ils se déclenchent. De plus en développement, on ne pense pas toujours à regarder si la table n'a pas plusieurs triggers qui se déclenchent...

Citation:

cela semble fonctionner mais le code suivant est il correct ?

Il faut garder une définition ensembliste de ton trigger. Ce dernier doit pouvoir continuer à fonctionner si on modifie plusieurs lignes à la fois.
Si tu fais un UPDATE sur deux ou trois lignes, le trigger ne déclenchera qu'une seule fois pour la table !
Donc il ne faut pas passer par des variables intermédiaires.

Ecrit plutôt ton trigger sous la forme :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 ALTER TRIGGER [dbo].[EgalZero] ON [dbo].[Magasin_P_Finis] AFTER UPDATE,INSERT AS SET NOCOUNT ON UPDATE [Magasin_P_Finis] SET [Magasin_P_Finis].transfert= case when [Magasin_P_Finis].transfert<0 then 0 else [Magasin_P_Finis].transfert end, [Magasin_P_Finis].[réserv]= case when [Magasin_P_Finis].réserv]<0 then 0 else [Magasin_P_Finis].[réserv] end, FROM [Magasin_P_Finis] INNER JOIN INSERTED ON INSERTED.Auto = [Magasin_P_Finis].Auto where inserted.transfert<0 or inserted.[réserv]<0
En plus de cette façon, tu fais les deux traitements en un seul passage sur la table. Donc tu auras de meilleurs performances.

15/12/2008, 20h33
Cyrille36

:yaisse2:
Merci mille fois Franck pour la qualité de la réponse et le temps passé à aider les autres. :king:

Cyrille.