Archives par mot-clé : SQL

Blogue

Suppression récursive sous MS SQL Server

Laisser un commentaire

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 prises en charge sous MS SQL Server. Avec une telle relation récursive, tenter de supprimer un enregistrement duquel un autre enregistrement dépend 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 enregistrements. 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 enregistrements recueillis sont supprimés.

(Adapté de devioblog)

Blogue

Boucler sur tous les enregistrements d’une table

Laisser un commentaire

Voici une façon simple et élégante (mais pas très rapide!) de boucler en SQL sur tous les enregistrements d’une table qui comporte une clé primaire unique:

-- changer type, id et table selon le type et le nom de la clé primaire et la table à manipuler
declare @id type
select @id = min(id) from table
while @id is not null
begin
-- modifier cette ligne selon l'opération à faire sur chaque enregistrement
select * from table where id = @id
select @id = min(id) from table where id > @id
end

Pour boucler sur tous les enregistrements d’une table qui ne comporte pas de clé primaire, la méthode est plus complexe et moins élégante; elle implique l’utilisation d’une table temporaire. Voirhttp://support.microsoft.com/kb/111401 pour plus de détails.

Géneral

Suppression récursive sous MS SQL Server

Laisser un commentaire

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)

	

	
	




	
	

				

			Géneral
		

			
Suppression récursive sous MS SQL Server

		

			 			Laisser un commentaire
						

	


		

		
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)

	

	
	




	
	

				

			Géneral
		

			
Boucler sur tous les enregistrements d’une table

		

			 			Laisser un commentaire
						

	


		

		
Voici une façon simple et élégante (mais pas très rapide!) de boucler en SQL sur tous les enregistrements d’une table qui comporte une clé primaire unique:


-- changer type, id et table selon le type et le nom de la clé primaire et la table à manipuler

declare @id type

select @id = min(id) from table

while @id is not null

begin

-- modifier cette ligne selon l'opération à faire sur chaque enregistrement

select * from table where id = @id

select @id = min(id) from table where id > @id

end


Pour boucler sur tous les enregistrements d’une table qui ne comporte pas de clé primaire, la méthode est plus complexe et moins élégante; elle implique l’utilisation d’une table temporaire. Voirhttp://support.microsoft.com/kb/111401 pour plus de détails.