Trigger avec action avant ET apres update de champ ?

[fredfred]

Bonjour,

Je voudrais savoir s'il est possible de créer un trigger sur un champ d'une table, et que ce trigger puisse agir avant et après l'écriture des données.

Je m'explique : j'ai une table Article avec QteStock et CMUP.
Quand QteStock est modifié, je voudrais que le trigger se déclenche et fonctionne de la manière suivante :

- avant écriture dans la base, on enregistre les valeurs QteStock et CMUP dans une table de travail
- mise à jour effective de la base
- après ecriture, on enregistre les nouvelles valeurs dans cette même table de travail.

Le but final étant d'avoir une table du genre :

Code :

  code_art  |   date_heure  |  qte_avant   |  cmup_avant   |  qte_apres   |   cmup_apres

avec une ligne à chaque fois que QteStock d'un article est modifié.

Est-ce que c'est jouable ?

Merci.

[Bluedeep]

Les triggers Sql Server (qui sont uniquement des triggers "commande" il n'y a pas de trigger "ligne" en SQL SERVER) exposent deux vues (enfin deux pour les trigger UPDATE) qui sont "delete" et "insert".

Il suffit de lire la vue "delete" pour obtenir les valeurs "anciennes" et la vue "insert" pour lire les valeurs "nouvelles" (c'est un peu l'équivalent de :old et :new en Oracle, mais qui ne sont dans le cas d'Oracle qu'accessible dans les triggers "ligne").

Il va de soit qu'un trigger "ON INSERT" n'expose que la vue "insert" et un trigger "ON DELETE" n'expose que la vue "delete".

Que ensuite tu utilises un trigger BEFORE ou AFTER dépend de tes choix applicatifs. (mais dans ton cas, ce serait plutôt un TRIGGER "AFTER", c'est à dire appelé après la mise à jour).

[SQLpro]

Envoyé par Bluedeep

Les triggers Sql Server (qui sont uniquement des triggers "commande" il n'y a pas de trigger "ligne" en SQL SERVER) exposent deux vues (enfin deux pour les trigger UPDATE) qui sont "delete" et "insert".

NON ! deleted et inserted

Il suffit de lire la vue "delete" pour obtenir les valeurs "anciennes" et la vue "insert" pour lire les valeurs "nouvelles" (c'est un peu l'équivalent de :old et :new en Oracle, mais qui ne sont dans le cas d'Oracle qu'accessible dans les triggers "ligne").

Il va de soit qu'un trigger "ON INSERT" n'expose que la vue "insert" et un trigger "ON DELETE" n'expose que la vue "delete".

Que ensuite tu utilises un trigger BEFORE ou AFTER dépend de tes choix applicatifs. (mais dans ton cas, ce serait plutôt un TRIGGER "AFTER", c'est à dire appelé après la mise à jour).

NON : il n'y a pas de trigger BEFORE dans SQL Server. Mais des trigger INSTEAD OF et AFTER.

A +

[Bluedeep]

Envoyé par SQLpro

NON ! deleted et inserted
NON : il n'y a pas de trigger BEFORE dans SQL Server. Mais des trigger INSTEAD OF et AFTER.

A +

Décidément, 8 mois sans toucher à SQL SERVER ne me réussissent pas....

[SQLpro]

et puis il va y avoir DENALI !!!!

;-)

A +

[fredfred]

J'ai essayé ça

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
 
ALTER TRIGGER [dbo].[EV_OCI_ARTSTOCK]
        ON  [dbo].[F_ARTSTOCK]
        FOR UPDATE
 AS
 BEGIN
 
declare @ref varchar(50)
DECLARE @old_qte int
DECLARE @oldMontSto money
 
 -- inutile si pas d'update de la colonne visée
 IF NOT UPDATE(AS_QTESTO)
 RETURN
 
 -- stocke les anciennes valeurs
 SELECT @ref = AR_REF 
 FROM INSERTED
 
 SELECT @old_qte = F_ARTSTOCK.AS_QteSto, @oldMontSto = F_ARTSTOCK.AS_MontSto
 FROM F_ARTSTOCK
 WHERE F_ARTSTOCK.AR_REF = @ref
 
 INSERT INTO OCI_MVTSTOCK (AR_REF, Heure, QTE_AV, CMUP_AV, QTE_AP, CMUP_AP) 
 SELECT INSERTED.AR_REF, getdate(), @old_qte, @oldMontSto, INSERTED.AS_QteSto, INSERTED.AS_MontSto
 FROM INSERTED
 
 -- rollback en cas d'erreur
IF @@Error <> 0
ROLLBACK TRANSACTION
 
END

Mais les valeurs AV et AP de la table OCI_MVTSTOCK sont identiques, je ne parviens pas à récupérer les valeurs avant l'update.

[SQLpro]

Déjà c'est pas bon, car vous avez des variables, alors qu'un trigger est ensembliste !

A +

[fredfred]

Envoyé par SQLpro

Déjà c'est pas bon, car vous avez des variables, alors qu'un trigger est ensembliste !

A +

???

Parce que j'ai mis des DEFINE ?

[Bluedeep]

Envoyé par fredfred

???

Parce que j'ai mis des DEFINE ?

Comme je te l'ai dit supra les trigger SQL sont des trigger COMMANDE !

ce qui signifie que les vue deleted et inserted (merci à SQLPro de m'avoir corrigé) peuvent contenir plusieurs lignes. Or, ici le SELECT .. INTO ne peut en traiter qu'une.

Inserted et deleted ne fonctionne pas comme :old et ;:new de Oracle qui ne contienne qu'une ligne puisque accessible uniquement depuis les trigger "ligne" (concept inexistant en SQL SERVER).

En clair si on fait par exemple :

Code :

UPDATE MyTable SET MyField = newValue WHERE MyKeyNonUnique = MyValue

Le trigger UPDATE de MyTable ne sera appelé qu'une fois quel que soit le nombre de lignes concernées et les vues deleted et inserted vont contenir toutes les lignes concernées.

[fredfred]

OK, pigé.

Du coup, j'ai réécrit mon trigger ainsi :

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
37
38
39
40
41
42
43
44
45
46
 
ALTER TRIGGER [dbo].[EV_OCI_ARTSTOCK]
        ON  [dbo].[F_ARTSTOCK]
        FOR UPDATE
 AS
 BEGIN
 
declare @ref varchar(50)
DECLARE @old_qte int
DECLARE @old_MontSto money
DECLARE @new_qte int
DECLARE @new_MontSto money
 
 	-- inutile si pas d'update de la colonne visée
 	IF NOT UPDATE(AS_QTESTO)
 	RETURN
 
     DECLARE INS_curseur CURSOR FORWARD_ONLY READ_ONLY FOR
     SELECT AR_REF, AS_QteSto, AS_MontSto
     FROM INSERTED
     OPEN INS_curseur
 
     FETCH NEXT FROM INS_curseur
     INTO @ref, @new_qte, @new_MontSto 
 
     -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
     WHILE @@FETCH_STATUS = 0
     BEGIN
 
         -- stocke les anciennes valeurs
         SELECT @old_qte = F_ARTSTOCK.AS_QteSto, @old_MontSto = F_ARTSTOCK.AS_MontSto
         FROM F_ARTSTOCK
         WHERE F_ARTSTOCK.AR_REF = @ref
 
         INSERT INTO OCI_MVTSTOCK (AR_REF, Heure, QTE_AV, CMUP_AV, QTE_AP, CMUP_AP) 
         VALUES (@ref, getdate(), @old_qte, @old_MontSto, @new_qte, @new_MontSto)
 
         -- rollback en cas d'erreur
         IF @@Error <> 0
         ROLLBACK TRANSACTION
 
         FETCH NEXT FROM INS_curseur
         INTO @ref, @new_qte, @new_MontSto 
   END
 
END

Mais je ne parviens toujours pas à récupérer les valeurs avant modif.

[SQLpro]

Nombreuses erreurs :
1) pas de close du curseur
2) pas de deallocate du curseur
3) un rollback dans une boucle dans un curseur va se traduire par des effets de bords inattendus...
3) pourquoi passer par un curseur qui est lent alors que vous pouvez faire cela sous forme de requêtes optimisées ?

A +

[fredfred]

Pour l'instant, c'est du test à l'arrache, s'il manque des bouts, ça n'est pas grave. La version finale ne ressemblera probablement pas du tout à ça.
Si un curseur est plus lent, alors soit, j'utiliserai volontier une requête.
Mais tant que je n'aurai pas de solution à mon problème, je ne perdrai pas de temps à blinder le code.

[fredfred]

ah, je viens juste de capter que les anciennes valeurs étaient stockées dans la vue DELETED.
Du coup, ça à l'air de fonctionner selon mes besoins.

[fredfred]

est-il possible qu'un même enregistrement se retrouve plusieurs fois dans DELETED / INSERTED, s'il a été modifié par plusieurs utilisateurs en même temps, ou qu'une même transaction le modifie plusieurs fois ?

[elsuket]

Bonjour,

Pour l'instant, c'est du test à l'arrache, s'il manque des bouts, ça n'est pas grave. La version finale ne ressemblera probablement pas du tout à ça.
Si un curseur est plus lent, alors soit, j'utiliserai volontier une requête.
Mais tant que je n'aurai pas de solution à mon problème, je ne perdrai pas de temps à blinder le code.

Pourquoi ne pas le faire d'entrée ??? c'est justement une perte de temps, il va vous falloir revenir dessus, ce que comme tout le monde vous ne ferez pas.

est-il possible qu'un même enregistrement se retrouve plusieurs fois dans DELETED / INSERTED, s'il a été modifié par plusieurs utilisateurs en même temps, ou qu'une même transaction le modifie plusieurs fois ?

Non, car la portée de telles vues est celle de la session.

Au passage le type money est plus connu pour produire des erreurs de calculs que pour autre chose.
Il vaut mieux utiliser le type decimal.

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 TRIGGER [dbo].[EV_OCI_ARTSTOCK]
        ON  [dbo].[F_ARTSTOCK]
        FOR UPDATE
AS
BEGIN
	SET NOCOUNT ON
 
 	-- inutile si pas d'update de la colonne visée
 	IF NOT UPDATE(AS_QTESTO)
 		RETURN
 
	INSERT INTO dbo.OCI_MVTSTOCK
	(
		AR_REF
		, Heure
		, QTE_AV
		, CMUP_AV
		, QTE_AP
		, CMUP_AP
	)
	SELECT		I.AR_REF
			, GETDATE()
			, D.AS_QteSto
			, D.AS_MontSto
			, I.AS_QteSto
			, I.AS_MontSto
	FROM		inserted AS I
	INNER JOIN	deleted AS D
				ON I.AR_REF = D.AR_REF 
END

Voilà, en une seule instruction ...
Voyez ce que vous avez fait avec votre curseur : il n'y a rien de plus crade qu'un curseur en bases de données relationnelles SQL.
J'ai également publié un article sur l'écriture ensembliste de triggers, que l'on trouve très simplement avec un moteur de recherche très connu.

Tout en SQL est ensembliste parce que ce langage est conçu pour cela.
Si vous faites du ligne à ligne (RBAR dans la littérature), ne vous attendez pas à des performances au moins correctes.

@++

[fredfred]

Envoyé par elsuket

Bonjour,
Pourquoi ne pas le faire d'entrée ??? c'est justement une perte de temps, il va vous falloir revenir dessus, ce que comme tout le monde vous ne ferez pas.

Et j'ai arreté de lire là !

On se connait ? on a déjà bossé ensemble ?
Me faire traiter de feignasse par un inconnu qui à la grosse tête quand je demande un peu d'aide, ça va 5 minutes.
OSEF, mon code est terminé depuis vendredi soir.

[elsuket]

Et j'ai arreté de lire là !

Tanpis pour vous
Je n'ai jamais bossé avec qui que ce soit sur ce forum

Dire que j'ai la grosse tête c'est vraiment mal me connaître, ce qui justifie qu'effectivement nous n'avons pas travaillé ensemble.
Si j'arrivais pas à passer les portes ou que j'aie les chevilles qui se touchent, expliquez-moi pourquoi j'ai pris la peine de vous donner le code du trigger.

Si vous avez envie de mal faire les choses, ça vous regarde.
Mais avec un aussi mauvais à priori, je suis finalement content de ne pas avoir à travailler avec vous.

OSEF, mon code est terminé depuis vendredi soir.

Impressionnant !

Allez bon boulot

@++

[iberserk]

on a déjà bossé ensemble ?

Honnêtement à vous lire et vu votre réaction çà ne donne pas envie... des développeurs avec votre raisonnement couplé d'un 'je vous emmerde' j'en croise plein dans ma boite... qu'il faut que je sauve le jour ou on arrive en prod et que leur 'truc à l'arrache mais je sais faire mieux...' amène des temps de réponses digne d'une page de CDISCOUNT...

[elsuket]

Honnêtement à vous lire et vu votre réaction çà ne donne pas envie... des développeurs avec votre raisonnement couplé d'un 'je vous emmerde' j'en croise plein dans ma boite... qu'il faut que je sauve le jour ou on arrive en prod et que leur 'truc à l'arrache mais je sais faire mieux...' amène des temps de réponses digne d'une page de CDISCOUNT...

Soit on a bossé dans la même boîte ... soit ... non ... NON ... y'en a partout des comme ça ?