Suppression récursive sous MS SQL Server

Supposons que vous ayez une table définie comme ceci:

CREATE TABLE MyTable (

 

  OID INT, --clé primaire

 

  OID_Parent INT, --récursion, clé étrangère

 

  --... autre colonnes

 

)

 


 

 

 

 

 

 

 

 

Bien qu’il soit possible de définir une clé étrangère avec l’attribut DELETE CASCADE, à la différence d’autres SGBD, les suppressions en cascade récursives sur la même table ne sont pas pris en charge sous MS SQL Server. Avec une telle relation récursive, tenter de supprimer un enregistrement duquel un autre enregistrement dépen n’est pas permis. Si pour contourner le problème vous tentez de créer un déclencheur INSTEAD OF DELETE, celui-ci ne se déclenche uniquement pour le premier DELETE. Il ne se déclenche pas récursivement pour supprimer tous les enregistrement. Ce comportement est attendu et documenté sur MSDN : “Si un déclencheur INSTEAD OF défini sur une table exécute une instruction portant sur cette table et qui est susceptible de l’activer de nouveau, il n’est pas appelé de façon récurrente.” La solution est donc de créer un déclencheur de suppression récursif comme celui-ci:

CREATE TRIGGER del_MyTable

 

  ON MyTable

 

  INSTEAD OF DELETE

 

AS

 

  CREATE TABLE #Table(

 

    OID INT

 

  )

 

  INSERT INTO #Table (OID) SELECT OID FROM deleted

 

  DECLARE @c INT

 

  SET @c = 0

 

  WHILE @c <> @@ROWCOUNT

 

  BEGIN

 

    SELECT @c = @@ROWCOUNT

 

    INSERT INTO #Table (OID)

 

    SELECT MyTable.OID FROM MyTable

 

    LEFT OUTER JOIN #Table ON MyTable.OID = #Table.OID

 

    WHERE MyTable.OID_Parent IN (SELECT OID FROM #Table)

 

    AND #Table.OID IS NULL

 

  END

 

  DELETE MyTable FROM MyTable INNER JOIN #Table ON MyTable.OID = #Table.OID

 

END

 


 

 

 

Ce déclencheur insère tous les enregistrements de la pseudo table deleted dans une table temporaire #Table. Ensuite, il rassemble tous les enregistrements qui ne sont pas déjà dans la table temporaire (LEFT OUTER JOIN … WHERE IS NULL). La boucle s’arrête si aucun nouvel enregistrement n’est trouvé. Finalement, tous les documents recueillis sont supprimés.

(Adapté de devioblog)

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *