Problème de triggers et dépendence fonctionnelle

[polux31]

Bonjour alors voici mon trigger

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
USE [geststockmazars]
GO
/****** Object:  Trigger [dbo].[suppr_four]    Script Date: 05/04/2011 16:56:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[suppr_four]
ON [dbo].[fournisseur] FOR DELETE
AS
declare
@id_four numeric(2,0)
begin
SELECT @id_four = nofour FROM deleted;
DELETE FROM consommable
WHERE reffour = @id_four
end

Je souhaitais qu'il supprime automatiquement dans ma table consommable qui contient tout mes consommables, tout les consommables qui ont pour reffour (reference forunnisuser) le fournisseur qu'on supprime de la table fournisseur.

Voilà mes tables :

consommable (noprod, proddesig, prodprix, qtestock, datemodif, codetype#, reffour#)
fournisseur (nofour, libfour, raisonsoc)
typeconso (notype, libtype)
users(id, login , pass, statut)

La base de données est consituée de 4 tables : consommable, fournisseur, typeconso et users :

La table consommable contient les différents consommables stockés dans la base de données
La table fournisseur répertorie les différents fournisseurs des consommables
La table typeconso recense elle les différents types de consommables
Enfin, la table users stocke les différents utilisateurs et leurs droits.

Le problème ne semble pas venir de mon trigger mais du fait que j'ai mis une clé étrangère entre ces 2 tables.
Quand je lenleve la suppression se fait et le trigger sexecute.
Comment faire pour que cela fonctionne sans que je n'ai à enlever cette clé étrangère ?Faire une suppression en cascade, chose que je ne sais pas faire avec sqlserver...



Merci d'avance !

[aieeeuuuuu]

Bonjour,


En effet, le suppression en cascade peut etre une bonne solution dans votre cas. Quelle est votre version de SQL Server ? (il me semble que cette fonctionnalité date de 2005 ?!?)

Vous pouvez alors ajouter "ON DELETE CASCADE" à la fin de votre définition de clef étrangère.


Cependant, il est normal également que votre trigger ne fonctionne pas :
Vous spécifiez un trigger "FOR DELETE", qui s’exécute donc après que la suppression a eu lieu, et donc après que les contraintes d’intégrité ont été vérifiées !
Dans votre cas, la suppression du fournisseur viole une contrainte d'integrité, votre ordre est donc annulé, et votre trigger n'est jamais executé.
Pour qu'il fonctionne tel que vous l'avez écrit, vous devriez faire un trigger INSTEAD OF, qui supprime d'abord les consommables, puis supprime les fournisseurs.

Pensez également qu'un ordre DELETE peut affecter plusieurs lignes, dans votre trigger, vous cherchez l'ID du fournisseur. Vous devriez donc plutôt faire une suppression directement à partir de la pseudo Table deleted :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
 
ALTER TRIGGER [dbo].[suppr_four]
ON [dbo].[fournisseur] 
INSTEAD OF DELETE
AS
BEGIN
 
    --supression des consommables
    DELETE FROM Consommable 
    FROM DELETED 
    WHERE Consommable.reffour = DELETED.nofour
 
    --suppression des fournisseurs
    DELETE FROM Fourniseeur
    FROM DELETED
    WHERE Fornisseur.nofour= DELETED.nofour
 
END

[polux31]

Tout d'abord merci pour votre réponse très complète.

Vous me proposez deux solutions.
Dois-je les appliquer toutes les deux ou une seule des deux suffit ?
J'entends par là que si je rajouter le on delete cascad cela suffira ou alors il faut que jemodifie aussi mon trigger ?

Merci d'avance !

[elsuket]

Bonjour,

Envoyé par aieeeuuuuu

En effet, le suppression en cascade peut etre une bonne solution dans votre cas. Quelle est votre version de SQL Server ? (il me semble que cette fonctionnalité date de 2005 ?!?)

Cela existe aussi dans la version 2000 ... et probablement dans les versions antérieures

Envoyé par polux31

Vous me proposez deux solutions.
Dois-je les appliquer toutes les deux ou une seule des deux suffit ?
J'entends par là que si je rajouter le on delete cascad cela suffira ou alors il faut que jemodifie aussi mon trigger ?

C'est l'un des deux. Je pencherai vers le CASCADE néanmoins.
Aieeeuuuuu a voulu vous expliquer pourquoi votre trigger ne fait pas ce que vous voulez qu'il fasse.

Le trigger tel que vous l'avez écrit n'est pas ensembliste : il est donc faux.

@++

[polux31]

Ok.
Si j'ajoute la fonction en cascade.
Un trigger comme cela irait ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
ALTER TRIGGER [dbo].[suppr_four]
ON [dbo].[fournisseur] 
INSTEAD OF DELETE
AS
BEGIN
 
    --supression des consommables
    DELETE FROM Consommable 
    FROM DELETED 
    WHERE Consommable.reffour = DELETED.nofour
 
 
END

Comme ça lorsqu'on delete sur ma table fournisseur automatiquement le trigger se lance et on delete les consommables.

C'est bien ça ?

[elsuket]

C'est l'une des deux solutions, mais pas les deux en même temps :
Si vous mettez CASCADE pour vos DELETE dans la contrainte, vous n'avez pas besoin du trigger, et inversement

Pour le trigger, on je préfère cette écriture :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
ALTER TRIGGER [dbo].[suppr_four]
	ON [dbo].[fournisseur] 
INSTEAD OF DELETE
AS
BEGIN
	SET NOCOUNT ON
 
	-- Supression des consommables
	DELETE		FROM dbo.Consommable 
	FROM		DELETED AS D
	INNER JOIN	dbo.Consommable AS C
				ON C.reffour = D.nofour 
END

Cependant attention, parce que ce trigger est un INSTEAD OF
Cela signifie que tel quel, les lignes de la table Consommable seront supprimées, mais pas les lignes de la table fournisseur.

En ce sens le code complet du trigger devrait être :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ALTER TRIGGER [dbo].[suppr_four]
	ON [dbo].[fournisseur] 
INSTEAD OF DELETE
AS
BEGIN
	SET NOCOUNT ON
 
	-- Supression des consommables
	DELETE		FROM dbo.Consommable 
	FROM		DELETED AS D
	INNER JOIN	dbo.Consommable AS C
				ON C.reffour = D.nofour 
 
	-- Suppression des fournisseurs
	DELETE		FROM dbo.Fourniseeur
	FROM		DELETED AS D
	INNER JOIN	dbo.Fournisseur AS F
				ON F.nofour = D.nofour
END

Dans tous les cas le trigger que vous a donné aieeeuuuuu est correct.
Il s'agit juste des jointures que j'ai horreur (mais c'est mon goût) de voir dans une clause WHERE.

Enfin le CASCADE sera moins consommateur de ressources que le trigger.
Mais lorsque vous supprimerez des lignes de la table Fournisseur, vous supprimerez également toutes les lignes de toutes les tables référençant la table Fournisseur qui ont pour valeur celle de clé primaire des lignes supprimées de la table Fournisseur.
Si ceci n'est pas le comportement que vous souhaitez avoir, alors créez le trigger.


@++

[polux31]

Je vais me pencher plus vers un trigger.
Cependant vous dites :

Code :

1
2
3
 
Cependant attention, parce que ce TRIGGER est un INSTEAD OF
Cela signifie que tel quel, les lignes de la TABLE Consommable seront supprimées, mais pas les lignes de la TABLE fournisseur.

Mais le but de ce trigger est qu'il se lance lors de la suppression d'une ligne de la table fournisseur donc la deuxième partie est inutile vu que forcément elle va être supprimée cette ligne dans la table fournisseur non ?

Je parle de cette partie du trigger

Code :

1
2
3
4
5
-- Suppression des fournisseurs
	DELETE		FROM dbo.Fourniseeur
	FROM		DELETED AS D
	INNER JOIN	dbo.Fournisseur AS F
				ON F.nofour = D.nofour

Merci beaucoup pour vos réponses et vos explications très claires selon moi.

Edit :
J'ai une autre question.
Mes tables sont

« consommable » (noprod, proddesig, prodprix, qtestock, datemodif, codetype#, reffour#)
« fournisseur » (nofour, libfour, raisonsoc)


En fait j'ai un problème sur un trigger ou je souhaite controler lors de l'emprunt de consommables si la quantité en stock est suffisante pour le nombre de consommables que la personne souhaite empruntée.
Le problème est que je ne sais pas trop comment faire pour un update...
Pouvez-vous m'aider ?

[aieeeuuuuu]

Mais le but de ce trigger est qu'il se lance lors de la suppression d'une ligne de la table fournisseur

il s'agit d'un trigger INSTEAD OF. Il ne s'execute pas lors mais à la place d'une commande DELETE (qui, au passage, peut concerner plusieurs lignes). La commande DELETE est en quelque sorte "ignorée". Il faut donc relancer l'ordre de suppression à l’intérieur du trigger.

[aieeeuuuuu]

pour votre problème de quantité en stock, il suffit d'ajouter une contrainte CHECK pour vérifier que la quantité en stock est positive :

Code SQL :

1
2
3
4
 
ALTER TABLE consommable
ADD CONSTRAINT CK_QTE_POSITIF 
CHECK (qtestock >= 0)

Évitez de passer par des triggers lorsque les contraintes peuvent être définies autrement, pour deux raisons principales :
1/ ce sera optimisé, (les contraintes CHECK sont faites pour cela, et ce sera toujours plus performant, aussi bon que soit votre trigger)

2/ le moteur pourra "comprendre" cette contrainte et s'en servir pour optimiser les plans de requête. Ainsi, avec une telle contrainte, et même sur une table de forte volumétrie, pour une requête telle que

Code SQL :

1
2
3
4
 
SELECT *
FROM consommables
WHERE qteStock < 0

le moteur sait (grâce à la contrainte) qu'aucune ligne ne peut répondre au filtre et n'aura pas besoin d'aller vérifier les données dans la table !

[polux31]

Ok ok merci pour ces précisions je vais essayer de réaliser ça !

[aieeeuuuuu]

Envoyé par elsuket

Enfin le CASCADE sera moins consommateur de ressources que le trigger.
Mais lorsque vous supprimerez des lignes de la table Fournisseur, vous supprimerez également toutes les lignes de toutes les tables référençant la table Fournisseur qui ont pour valeur celle de clé primaire des lignes supprimées de la table Fournisseur.

Heu... seulement si "ON DELETE CASCADE" est spécifié pour chaque clef étrangère des tables référençant Fournisseur.
polux31 peut spécifier la suppression en cascade sur la contrainte de la table consommable, sans pour autant le spécifier également sur la table Facture(par exemple), référençant également fournisseur.
1/ En cas de suppression d'un fournisseur qui a des consommables mais pas de facture, les consommables sont supprimés
2/ En cas de suppression d'un fournisseur qui a des factures, l'ordre est annulé pour violation de contrainte.

Je pense qu'un "ON DELETE CASCADE" serait parfait ici, sauf si polux31 veut ajouter des conditions "métier" à la suppression en cascade, dans quel cas il faut en effet passer par un trigger (par exemple ne supprimer les consommables rattachés au fournisseur QUE si le stock est à 0 pour chaque consommable...)