Discussion :

[windmastr26]

Bonjour à tous,

Je suis en train de convertir une base de données HF en base de données SQL Server.

J'ai pas mal de relations entre les tables et j'aimerai, en toute logique, faire des mises à jour/suppression en cascade. Par exemple : la suppression d'un client va engendrer la suppression de ses contrats, et la suppression de ses contrats va engendrer la suppression des lignes des contrats respectifs...

Voici comment je traduit cela en SQL :

Code :

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

1
2
3
4
CREATE TABLE Contrats (
   Cont_num INTEGER IDENTITY,
   Cont_clicode VARCHAR(10) CONSTRAINT FK_Contrats_clicode FOREIGN KEY (Cont_clicode) REFERENCES Clients (Cli_code) ON UPDATE CASCADE ON DELETE CASCADE
)

Seulement, j'ai souvent des messages d'erreurs qui m'indiquent que je ne peux pas créer de multiples contraintes de mise à jour en cascade :

L'introduction d'une contrainte FOREIGN KEY 'FK_Contrats_clicode' sur la table 'Contrats' peut provoquer des cycles ou des accès en cascade multiples. Spécifiez ON DELETE NO ACTION ou ON UPDATE NO ACTION, ou modifiez d'autres contraintes FOREIGN KEY.

De ce que j'ai lu en parcourant le forum, il est dangereux d'utiliser les mises à jour en cascade en raison du blocage d'enregistrements.

Je trouve cela dommage car dans ce cas comment mettre en application ma contrainte ? En passant par des triggers ? Mais le problème ne va-t'il pas être le même ?

Est-ce que quelqu'un aurait un discours à me proposer sur cette question ? Un article qui traite de cela ? Afin que je puisse apprendre et avancer dans cette problématique.

Merci d'avance

[Sergejack]

À ce niveau SQL Server est loin d'être parfait avec ces mécanismes de détection de conflit trop sensibles.

Je pense que le plus simple est d'avoir des clause "ON DELETE SET NULL" (et que les colonnes soient nullable du coup) et d'utiliser des triggers AFTER DELETE pour faire le nettoyage.

[windmastr26]

En fait je suis finalement parti du principe qu'il est rare qu'un ID d'enregistrement change (du moins l'utilisateur ne pourra pas le changer), et comme je n'ai pas d'autres traitements à faire à la mise à jour d'un enregistrement : à quoi bon faire un "ON UPDATE CASCADE" ?

Pour ce qui est du "ON DELETE" je l'ai traduit par des triggers.

[Sergejack]

Je ne vois pas de scénario où "ON UPDATE CASCADE" ne traduirait pas le mauvais design qui consiste à avoir des clés primaires qui changent.

[windmastr26]

Je ne remet pas en cause l'utilité du "ON UPDATE". Seulement dans le cadre de l'application que je dois développer, les utilisateurs n'auront pas l'occasion de modifier les ID des enregistrements, du coup il n'y a pas d'utilité en mon sens.

[SQLpro]

De toute façon le DELETE CASCADE est fortement à déconseiller car n'oubliez pas que toutes les tables impactées seront verrouillées durant la transaction du DELETE. En gros si vous avez des clients, des factures, des lignes de factures, des commandes, des lignes de commandes, des bon de livraison, des lignes de BL.. cela fera 7 tables dérouillées dans la transaction et le tout journalisé, alors qu'en déportant votre delete aux heures creuses par un SET NULL ou SET DEFAULT, cela ira beaucoup mieux....

A lire sur ce sujet : http://blog.developpez.com/sqlpro/p8...plexes-dans-l/

A +

[Sergejack]

De toute façon le DELETE CASCADE est fortement à déconseiller car n'oubliez pas que toutes les tables impactées seront verrouillées durant la transaction du DELETE. En gros si vous avez des clients, des factures, des lignes de factures, des commandes, des lignes de commandes, des bon de livraison, des lignes de BL.. cela fera 7 tables dérouillées dans la transaction et le tout journalisé, alors qu'en déportant votre delete aux heures creuses par un SET NULL ou SET DEFAULT, cela ira beaucoup mieux....

A lire sur ce sujet : http://blog.developpez.com/sqlpro/p8...plexes-dans-l/

A +

Et est-ce qu'avoir un trigger "AFTER" diffère la prise de lock ?

[SQLpro]

Non, c'est pire, le déclencheur étant dans la transaction.

Tout ceci devrait être fait en delete logique avec une vue et faire les delete physique aux heures creuses.

le problème c'est que 99% des développeurs oublient de modéliser le modèle externe de données (vues et SP...)

A +

[windmastr26]

le problème c'est que 99% des développeurs oublient de modéliser le modèle externe de données (vues et SP...)

Je plaide coupable...

Mais merci pour l'article en lien, très utile et on y pense pas toujours...

[Sergejack]

Non, c'est pire, le déclencheur étant dans la transaction.

Je ne suis pas sûr de comprendre.
C'est pire comment et pourquoi ?

[SQLpro]

Dans un trigger vous êtes à l'intérieur d'une transaction. Donc les lignes impactées par le mise à jour ne sont pas encore validées. De ce fait un verrou est posé sur les lignes en cause et perdure pendant tout le traitement effectué dans le trigger !

A +

[Sergejack]

Dans un trigger vous êtes à l'intérieur d'une transaction. Donc les lignes impactées par le mise à jour ne sont pas encore validées. De ce fait un verrou est posé sur les lignes en cause et perdure pendant tout le traitement effectué dans le trigger !

A +

D'accord mais n'est-ce pas autant le cas pour les ON DELETE/UPDATE CASCADE ?

[SQLpro]

Oui, mais c'est optimisé... Il y a une différence sensible de durée du traitement entre du code SQL interprété et le code C++ d'un moteur relationnel très optimisé. En sus, les contraintes sont préventives (agissent "avant" la mise à jour effective) alors que la majorité des déclencheurs agissent après.

A +

[Sergejack]

C'est bon à savoir, merci pour l'info.