Discussion :

[Cyrille36]

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 :

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

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

[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.

[Franck SORIANO]

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

Code sql :

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

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.

[Cyrille36]

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 :

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

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 :

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

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 :

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

[Franck SORIANO]

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

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

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

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.

[Cyrille36]

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

Cyrille.