Discussion :

[stephane.julien]

Bonjour,

J'ai une table qui est la cible d'une clé étrangère sur laquelle j'aimerais avoir un DELETE ON CASCADE. Par contre, j'ai un TRIGGER INSTEAD OF DELETE sur cette même table cible.

Je me suis rendu compte que cela n'est pas possible, est-ce juste ou il existe une parade à cela? Je vois pour l'instant deux solutions, mais j'aimerais votre avis :

1. Suppression du DELETE ON CASCADE, et remplacement de celui-ci par des TRIGGERS
2. Suppression du DELETE ON CASCADE, et suppression de la première table uniquement via procédure stockée, qui va s'occuper de supprimer les tuples de la table cible.

Je tiens à préciser que c'est un endroit dans ma base de données où il y aura un très très fort trafic, et que ce endroit nécessite une performance très élevée. S'il existe d'autres possibilités qui augmentent la performance, je suis preneur, quelque soit la complexité d'implémentation.

Merci d'avance de votre aide et de votre avis.

[SQLpro]

Essayez en activant la récursivité des triggers, sinon, il est fortement déconseillé d'utiliser le mode CASCADE... Surtout si vous envisagez d'utiliser les trigger INSTEAD OF afin de faire du mapping RO !

Je vous conseille en revanche de faire la chose suivante :
1) n'utiliser que des vues pour vos accès client (application)
2) utiliser le mode de gestion SET NULL, si possible ou SET DEFAULT dans le cas contraire
3) placez un filtre dans votre vue afin d'éliminer les lignes orphelines. Par exemple WHERE FK IS NOT NULL ou WHERE FK > 0
4) dans la cas du SET DEFAULT, créez une référence bidon d'id 0 et filtrez les vues maître de la même manière
5) faites passer de nuit un batch SQL qui part à la recherche des lignes orphelines de toutes les tables impactées par des DELETE logique et les supprime physiquement.

C'est ainsi, avec un pur respect de l'essence même des concepts du relationnel (utilisation du schéma externe, c'est à dire des vues) que vous aurez les meilleures performances, sur des bases fortement sollicitées et dotées d'une grande volumétrie.

A +

[stephane.julien]

Merci de votre réponse !

Je m'étais en effet rendu compte que le DELETE ON CASCADE n'étais pas la bonne solution. Je suis content d'en avoir une confirmation.

Je ne connais pas encore la récursivité au niveau des TRIGGERS. Pourriez-vous me donner une piste par rapport à cet aspect? Je ne vois pas comment je peux résoudre mon problème avec cet outil...

Merci d'avance !

[SQLpro]

ALTER DATABASE .... SET RECURSIVE_TRIGGERS { ON | OFF }

Voir aussi les "nested triggers" au niveau de sp_configure.

A +

[stephane.julien]

J'ai finalement réécrit la cascade avec des INSTEAD OF DELETE. Par contre pour des raisons de performance, j'ai un colonne DELETED dans ma table que je mets à TRUE, puis un batch la nuit supprime les lignes dont DELETED=1. Cela se fait sur un setting d'application (soit suppression en live, soit différée). La différence de performance est assez impressionnante !

[SQLpro]

Bravo, c'est effectivement une conception intéressante. Je vous conseille en sus de passer par des vues ! et rendre vos tables inaltérables et non misajourable par le biais des privilèges.

A +