LLI Wiki — My Digital Knowledge Base

Simuler une suppression en cascade avant de supprimer des lignes

Procédure T-SQL qui génère les DELETE des tables enfants d'une ligne parent et compte les lignes concernées, sans rien supprimer.

Lorsqu'une clé étrangère n'a pas de ON DELETE CASCADE, les lignes enfants doivent être supprimées en premier. La procédure ci-dessous construit les instructions DELETE qui supprimeraient une ligne parent et ses lignes enfants directes, et compte les lignes que chaque instruction supprimerait. Elle ne supprime rien : elle renvoie les instructions sous forme de lignes, que vous exécutez vous-même.

Procédure

CREATE PROCEDURE   dbo.GenerateCascadingDeletes
  @ParentTable     nvarchar(512),
  @ParentColumn    sysname,
  @DeleteCriteria  nvarchar(255)
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql nvarchar(max) = N'',
          @src nvarchar(max) = N'SELECT ''DELETE $t$ WHERE $c$ $clause$;'' UNION ALL SELECT 
               ''-- This would delete '' + (SELECT RTRIM(COUNT(*)) FROM $t$ WHERE $c$ $clause$) 
               + '' rows.'';';
  -- generate deletes for child tables:
  SELECT @sql = @sql 
    + REPLACE(REPLACE(REPLACE(@src,N'$t$',t),N'$c$',c),N'$clause$',@DeleteCriteria)
  FROM
  (
    SELECT t = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
             + '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
           c = QUOTENAME(pc.name)
    FROM sys.foreign_key_columns AS pt
    INNER JOIN sys.columns AS pc
       ON pt.parent_object_id = pc.[object_id]
      AND pt.parent_column_id = pc.column_id
    INNER JOIN sys.columns AS rc
       ON pt.referenced_column_id = rc.column_id
      AND pt.referenced_object_id = rc.[object_id]
    WHERE pt.referenced_object_id = OBJECT_ID(@ParentTable)
      AND rc.name = @ParentColumn
  ) AS x;
  -- final delete of parent table:
  SELECT @sql += REPLACE(REPLACE(REPLACE(@src,N'$t$',@ParentTable),
    N'$c$',@ParentColumn),N'$clause$',@DeleteCriteria);
  EXEC sys.sp_executesql @sql;
END
GO

Utilisation

exec dbo.GenerateCascadingDeletes '[app].ParentTable', 'ParentId', '= 1001'

select ChildId, ParentId from app.ChildTable where ParentId = 1001

exec dbo.GenerateCascadingDeletes 'app.ChildTable', 'ChildId', '= 2001'
exec dbo.GenerateCascadingDeletes 'app.ChildTable', 'ChildId', '= 2002'

Le jeu de résultats liste, pour chaque table enfant, une instruction DELETE précédée d'un commentaire indiquant le nombre de lignes, puis le DELETE de la table parent. Exécutez les instructions dans l'ordre renvoyé : les enfants d'abord, le parent en dernier.

Remarques

Mastodon LLIMastodon SocialFosstodon