Discussion :

[Alex01]

Bonjour à tous et à toutes;

Je viens enfin de faire fonctionner mes triggers DELETE, UPDATE et INSERT mais j'ai un souci lors de la suppression multi-lignes du type : DELETE FROM maTable.

J'explique :
J'ai deux tables qui sont ECRITURE et CONSOLIDATION; a chaque ajout, suppression ou modification d'une ligne dans la table ECRITURE je veux que la table CONSOLIDATION soit mise à jour. Ecriture par écriture cela fonctionne parfaitement bien pour les INSERT, les UPDATE et les DELETE (trois triggers); mais lorsque j'effectue des modifications multiples dans la table ECRITURE, la table CONSOLIDATION se met partiellement à jour (exemple de la requête : DELETE FROM ECRITURE)

Voici le code du trigger (traitement après suppression) :

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].[MAJ_DELETE] 
   ON  [dbo].[ECRITURE]
   AFTER DELETE
 
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
 
	DECLARE @Annee int;
	DECLARE @Mois int;
 
	--Récupération des valeurs ANNEE et MOIS de la ligne d'ECRITURE supprimée
 
	SELECT @Annee = E.ANNEE , @mois = E.MOIS FROM deleted E
 
	--Mise à jour des montants de CREDIT et de DEBIT de la table CONSOLIDATION
 
	EXECUTE dbo.CONSOLIDATION_MAJ @Mois, @Annee
 
END

Voici le code de la procédure stockée CONSOLIDATION_MAJ :

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
 
ALTER PROCEDURE [dbo].[CONSOLIDATION_MAJ]
	-- Add the parameters for the stored procedure here
	@Mois int,
	@Annee int
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	UPDATE CONSOLIDATION SET 
		CREDIT = (SELECT COALESCE(SUM(COALESCE(E.CREDIT, 0)),0) FROM ECRITURE AS E WHERE (E.ANNEE = @Annee and E.MOIS = @mois)),
		DEBIT = (SELECT COALESCE(SUM(COALESCE(E.DEBIT, 0)),0) FROM ECRITURE AS E WHERE (E.ANNEE = @Annee and E.MOIS = @mois))  
	WHERE (CONSOLIDATION.ANNEE = @Annee and CONSOLIDATION.MOIS = @mois)
 
END

Si quelqu'un peut m'expliquer le problème ça serait super
Par avance merci;
Alex01

[soazig]

Bonjour,
Quand tu fais un update de plusieurs lignes ta table deleted comprend plusieurs lignes également, si tu veux tu peux les traiter unitairement avec un curseur ou de façon ensembliste.
Exemple de traitement ensembliste

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
UPDATE C
SET 
   Credit=e1.credit,
   Debit=e1.debit
from CONSOLIDATION C
inner join 
	(	select distinct d1.annee, d1.mois
		from
		Deleted d1) d
	on c.annee=d.annee
	and c.mois=d.mois
inner join 
(
	select e.Annee, e.Mois, COALESCE(SUM(COALESCE(E.CREDIT, 0)),0) as Credit,
	COALESCE(SUM(COALESCE(E.DEBIT, 0)),0) Debit
	from Ecriture e
	group by e.Annee, e.Mois,
) e1
on d.annee=e1.annee
and d.mois=e2.mois

Accessoirement ton trigger ne mettra à jour ta table consolidation qu'en cas de delete, pas en cas d'insert ou d'update
PS : Ma requete ne donne pas tout à fait le résultat car elle ne remet pas à 0 les mois sans ecriture. A toi d'adapter.
Cordialement
Soazig

[SQLpro]

De plus il est inutile de faire trois triggers. Un seul trigger peut être codé :

Code :

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

1
2
3
4
5
6
CREATE TRIGGER [dbo].[MAJ_ECRITURE] 
   ON  [dbo].[ECRITURE]
   AFTER INSERT, UPDATE,DELETE
AS
SET NOCOUNT ON;
...

A +

[Alex01]

Bonjour et merci de vos réponses;

Un peu plus inspiré ce matin, je suis finalement passé par les curseurs :

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
DECLARE @Annee int;
	DECLARE @Mois int;
 
	DECLARE curseur_deleted CURSOR FOR
	  SELECT E.ANNEE , E.MOIS FROM deleted E;
 
    OPEN curseur_deleted;
 
    FETCH curseur_deleted INTO @Annee, @Mois;
 
    WHILE @@FETCH_STATUS = 0
      BEGIN
        EXECUTE dbo.CONSOLIDATION_MAJ @Mois, @Annee;
        FETCH curseur_deleted INTO @Annee, @Mois;
	  END
 
    CLOSE curseur_deleted;
    DEALLOCATE curseur_deleted;

Et là ça fonctionne parfaitement !
Je vous remercie pour votre assistance ...

Alex01

[SQLpro]

Mieux vaut ouvrir votre curseur en mode : LOCAL FORWARD_ONLY STATIC et READ_ONLY sinon vous allez verrouiller et mobiliser beaucoup trop de ressources et potentiellement générer des attentes, des blocages, de la contention et des verrous mortels.

A +

[aieeeuuuuu]

Bonjour

Globalement, il serait préférable de transformer la table CONSOLIDATION en vue, et de l'indexer si nécessaire.

vous pourriez alors vous passer des ces déclencheurs et les performances seraient certainement meilleures